Course contentsShow
← System Design
Lesson 278 of 1,9198. Database Performance OptimizationFree lesson

Index Strategy for Large Tables

Choosing the right columns to index based on query patterns, selectivity, and cardinality at scale.

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 on A alone, but not B alone
  • Don't over-index — each index slows down INSERT/UPDATE/DELETE operations

Key Takeaway: Index columns with high selectivity and cardinality that match your most frequent query patterns, but balance write performance by avoiding unnecessary indexes.