Course contentsShow
← Data Science
Lesson 1080 of 2,14527. Database Design and OptimizationFree lesson

When to Create an Index

Identify columns that benefit from indexing: frequently filtered, joined, or sorted columns with high cardinality.

When to Create an Index

You'll learn how to identify which columns are ideal candidates for indexing to maximize database performance.

The Core Idea

Not every column deserves an index. Creating too many indexes wastes storage and slows down inserts and updates. The art is choosing columns where indexing delivers the biggest performance wins.

Think of an index like a specialized phone book. You wouldn't create a separate directory for every possible search (by shoe size, favorite color, etc.)—only for the lookups you actually perform frequently.

Columns That Benefit From Indexing

Frequently Filtered Columns

If a column regularly appears in WHERE clauses, it's a strong candidate:

-- If you frequently run this query:
SELECT * FROM orders WHERE customer_id = 12345;
-- Consider indexing: customer_id

Join Columns

Columns used in JOIN conditions benefit hugely from indexes. Both sides of the join should typically be indexed:

-- Foreign key columns in joins:
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- Index both: orders.customer_id AND customers.id

Sorted Columns

Columns in ORDER BY clauses can use indexes to avoid expensive sorting operations.

High Cardinality Matters

Cardinality = number of unique values. Columns with many distinct values (like email addresses or order IDs) benefit most. Low-cardinality columns (like boolean flags or status fields with 3-4 values) gain less from indexing.

When NOT to Index

  • Rarely queried columns
  • Very small tables (full scans are fast enough)
  • Columns with low cardinality
  • Columns that change frequently in write-heavy tables

Key Takeaway: Index columns that are frequently filtered, joined, or sorted—especially those with high cardinality—but avoid over-indexing to maintain write performance.