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.