How do database indexes work? How do you decide which columns to index?

How Indexes Work

A database index is a data structure that speeds up queries. The most common type is the B-Tree index — similar to a book's table of contents, it lets the database jump directly to the data instead of scanning every row.

B-Tree Index in Action

SELECT * FROM users WHERE email = 'alice@example.com';

• Without index: Full Table Scan — row by row, O(n)
• With index: B-Tree binary search, O(log n)

When to Add an Index

Good candidates:

• Columns frequently used in WHERE clauses (e.g. email, status, created_at)
• Foreign key columns used in JOINs
• Columns used in ORDER BY / GROUP BY
• High-cardinality columns: more unique values = more selective = better index benefit

Poor candidates:

• Low-cardinality columns (e.g. gender, is_deleted): low selectivity
• Heavily written columns: every INSERT/UPDATE/DELETE must update the index
• Rarely queried columns

Composite Indexes (Leftmost Prefix Rule)

CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

-- Effective: WHERE user_id = ?
-- Effective: WHERE user_id = ? AND status = ?
-- Ineffective: WHERE status = ?  (skips the first column)

Common Pitfalls

• Index ignored: WHERE YEAR(created_at) = 2024 — wrapping a column in a function disables index use; use range queries instead
• Covering index: when all queried columns exist in the index, the DB avoids the table lookup entirely (fastest possible)
• EXPLAIN: always run EXPLAIN SELECT ... to verify the query plan