Index Strategy for Large Tables
What you'll learn: How to choose which columns to index in large tables so your queries stay fast, even at scale.
Why Index Strategy Matters
An index is like a book's table of contents — it helps you jump straight to the right data instead of scanning every page. But unlike a small book, large database tables can have millions or billions of rows. The wrong indexes waste storage and slow down writes, while missing indexes make queries crawl.
Key Factors in Index Selection
Query Patterns
Index columns that appear frequently in WHERE clauses, JOIN conditions, and ORDER BY statements. If your queries always filter by user_id, that column needs an index. Analyze your explain plans (from the previous lesson) to see which queries do full table scans.
Selectivity
Selectivity measures how unique values are in a column. High selectivity (many unique values like email or order_id) makes great indexes — the database eliminates most rows quickly. Low selectivity (like status with only "active/inactive") helps less because each index lookup still returns tons of rows.
Cardinality
Cardinality is the count of distinct values. Columns with high cardinality (approaching the total row count) benefit most from indexing. A boolean column (cardinality = 2) rarely deserves its own index.
Practical Guidelines
- Index foreign keys used in joins
- Composite indexes for multi-column filters (
user_id, created_at) - Leading column matters — index
(A, B)helps queries filtering onAalone, but notBalone - Don't over-index — each index slows down
INSERT/UPDATE/DELETEoperations
Key Takeaway: Index columns with high selectivity and cardinality that match your most frequent query patterns, but balance write performance by avoiding unnecessary indexes.