Interview replay
Full round replay — database indexes
Timed verbal replay with pass/fail criteria per follow-up.
How to run this
The opener
“How does a database index actually work?”
Budget: 45 seconds. Going long here is itself a fail signal.
Follow-ups
1. “Is it like a hash map?”
Testing: The distinction that unlocks half the topic.
Scoring
Pass: No. A hash index handles equality only. A B+tree keeps entries in sorted order, so the same index also serves ranges, ORDER BY, MIN, MAX and prefix LIKE.
Fail: Accepts the analogy, which then makes the ORDER BY question impossible.
2. “Why can an index serve LIKE 'abc%' but not LIKE '%abc'?”
Testing: Do they reason from sortedness?
Scoring
Pass: Sorted by prefix, so everything starting with abc is contiguous and can be descended to. Everything ending in it is scattered through the index, so there is no range.
Fail: Says wildcards are just unsupported.
3. “Clustered versus non-clustered?”
Testing: Physical order, and the second read.
Scoring
Pass: A clustered index IS the physical order of the rows, so there is only one — in InnoDB it is the primary key. A non-clustered index is separate and its leaves hold a pointer, so using it usually costs a second read per row.
Fail: Describes them as two kinds of lookup speed.
4. “What is a covering index and why does it matter?”
Testing: The most useful tuning idea in the topic.
Scoring
Pass: One containing every column the query needs, so the row is never fetched. It removes a random read per matching row, which is often where the time actually goes.
Fail: Calls it an index with more columns, with no mention of the row fetch.
5. “What does each additional index cost?”
Testing: Whether they will argue against indexes.
Scoring
Pass: Space, plus a write to that index on every insert, delete and update of its columns, plus page splits. Eight indexes means an insert costs roughly nine writes, so an unused index is pure loss.
Fail: Says indexes only cost disk.
6. “You added an index and the query is still slow. What now?”
Testing: Structured diagnosis.
Scoring
Pass: Check the plan. Usually the index is unusable — a function or a cast on the column, or a leading wildcard — or the planner chose a scan because selectivity is low. A big gap between estimated and actual rows means stale statistics instead.
Fail: Adds another index.
7. “Why would the planner ignore a perfectly good index?”
Testing: Can they defend the planner?
Scoring
Pass: Because it is right. If a value matches a large fraction of the table, using the index means that many random reads, and a sequential scan is cheaper. Selectivity, not the existence of the index, decides.
Fail: Treats it as a planner bug or a statistics problem only.
8. “Would you index a boolean or a status column?”
Testing: Selectivity applied.
Scoring
Pass: Usually not — few distinct values means any lookup matches a large share of the table. If the distribution is very skewed, a partial index on the rare value is the right tool.
Fail: Says yes because it is filtered on often.
9. “You have indexes on (customer_id) and (customer_id, status). Keep both?”
Testing: The redundancy rule.
Scoring
Pass: No. The narrow one is a leftmost prefix of the wider one, so anything it serves the composite serves too. Drop it and save a write per insert. An index on (status) alone would not be redundant.
Fail: Keeps both in case one is faster.