Indexing interview questions

B-tree mechanics, composite-column order, covering indexes, and why an index goes unused.

4 questions in Databases & SQL.

Databases & SQLA 22-minute read on how the topic fits together, before the questions.Database fundamentals58 short answers on one page, for revising rather than studying.

hard4

hardConceptScenarioDesign

A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?

A partial index only contains rows matching its predicate, so indexing WHERE deleted_at IS NULL stores five million entries instead of forty million - smaller, shallower, and far likelier to stay cached. The catch is the planner only uses it when it can prove the query predicate implies the index predicate, and it does nothing about dead rows still in the heap.

5 minmid, senior, staff

hardConceptDesign

Three queries hit the same table with different filters. What composite indexes do you build, and how do you order the columns?

Order by how the predicate uses the column, not by cardinality: equality columns first, then one range column, then columns needed only for sorting. A range predicate stops the index seeking on everything after it, and the leftmost prefix decides which other queries the index can serve.

6 minmid, senior, staff

hardScenarioConcept

This table has fourteen indexes and writes have got slower. How do you work out which ones to drop?

Decide from evidence, not intuition: per-index scan counts read against the date the statistics were reset, redundancy judged by leading columns, and the constraints and foreign keys that need an index whether or not anything ever scans them. Then make the drop reversible.

6 minmid, senior, staff

hardScenarioConcept

A query has an index on the filtered column but the plan shows a sequential scan. Walk me through how you would diagnose it.

Work outward from the plan: confirm the index is actually usable for the predicate as written, then check whether the optimiser is choosing to ignore it because of selectivity, stale statistics, or a type or collation mismatch that makes the predicate non-sargable.

3 minmid, senior, staff