Databases & Transactions interview question · Most asked
How do database indexes work and when do they help or hurt?
Summary
Most indexes are B-trees that keep column values sorted with pointers to rows, turning full-table scans into logarithmic lookups for equality, range and ORDER BY queries. They cost extra storage and slow down writes, because every insert/update must maintain them.
Longer Answer
Index columns used in WHERE, JOIN and ORDER BY with good selectivity. Composite index column order matters (leftmost prefix rule). Covering indexes include all needed columns so the table isn't touched. Functions on columns (WHERE lower(email) = ...) need expression indexes. Verify with EXPLAIN ANALYZE and remove unused indexes.
Key Points
- B-tree: sorted, log-time lookups
- Help reads, cost writes
- Composite: leftmost prefix
- Covering and expression indexes
- Verify with EXPLAIN
More most-asked Databases & Transactions questions
- What does ACID mean?
- Explain transaction isolation levels and the anomalies they prevent.
- What is the N+1 query problem and how do you fix it?
- How do you choose between SQL and NoSQL databases?
- What is connection pooling and how do you size a pool?
- How do you run schema migrations without downtime?
- How do you keep data consistent across services without distributed transactions?