What is a database index, how does it speed up queries, and what does it cost?
What they are really testing: Whether you understand an index trades write speed and storage for read speed, and that you index selectively. "Add an index" is a reflex; knowing the cost is the signal.
A real interview question
What is a database index, how does it speed up queries, and what does it cost?
What most people say
drag me
“An index makes queries faster, so you add indexes to speed up your database.”
It knows the benefit but not the cost. Indexes slow writes and consume storage, so indexing everything backfires. Knowing the trade-off and indexing selectively is what the question is really probing.
The follow-ups they ask next
Why not just index every column?
Every index adds storage and slows every write (each insert/update maintains all relevant indexes). On write-heavy tables that is a real cost, and unused indexes are pure overhead. Index only what queries actually use.
How do you confirm a query is using an index?
Run EXPLAIN (or EXPLAIN ANALYZE): the plan shows an index scan vs a sequential/full table scan. If it is scanning when you expected an index, the index is missing, unusable, or not selective enough.
What the interviewer is listening for
- Knows it is a B-tree turning scans into log-n lookups
- Names the write/storage cost
- Indexes selectively + verifies with EXPLAIN
What sinks the answer
- "Indexes are free speed"
- Would index everything
- No notion of write cost or EXPLAIN
If you genuinely do not know
Say this instead of freezing. Reasoning out loud from what you do know beats silence every single time, and a good interviewer is listening for exactly that.
“An index is [a B-tree that finds rows by a column without scanning the whole table, turning O(n) scans into ~O(log n) lookups]. Cost: [storage and slower writes, every write maintains the indexes]. So [index only columns you filter/join/sort on, use composite indexes carefully, and verify with EXPLAIN], not index everything.”
Keep going with databases
Foundation
What are the ACID properties of a database transaction?
Foundation
What is normalization, and what are primary and foreign keys?
Junior
A query is slow in production. How do you diagnose and fix it?
Junior
Why is database connection pooling important, especially at scale?
Junior
How do read replicas help scale a database, and what is replication lag?
Mid
Under load, your RDS instance is the bottleneck and the app is slowing down. Walk me through your options, in order.
Knowing the answer is not the same as recalling it under pressure
Sign in to send the questions you fumble to spaced recall, so they come back right before you would forget them, and learn the concepts behind them with hands-on labs.
Start free