DATABASES GUIDE
Database Indexing Explained for Interviews
By the HireReadyAI team · 8 min read
Why indexes exist, composite and covering indexes, and the write costs people forget.
Why indexes exist
Indexes trade write overhead and memory for faster reads. Without them, filters become scans. With the wrong ones, writes slow down and the optimizer gets confused.
Practical types
B-tree indexes dominate OLTP. Composite indexes follow leftmost prefix rules. Covering indexes satisfy queries from the index alone. Partial/filtered indexes help when available in your engine.
Costs
Every index slows inserts/updates and uses storage. Unused indexes are pure cost. Measure with EXPLAIN and production stats before adding more.
Good habits
Index for the query you run, not for theoretical columns. Review slow query logs monthly. In interviews, walk through a concrete query and the index you would add.