Top 7 Indexing Interview Questions
Browse 7 real-world technical and behavioral interview questions about Indexing. Review scenarios, edge cases, and architectural best practices.
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.
You need every driver within two kilometres of a moving rider, updated every four seconds. What index makes that cheap?
A geospatial index for nearby drivers reduces latitude and longitude to ordered cells such as geohash, S2 or H3. Query the rider's cell plus neighbours, keep the hot current-position index in memory because positions churn, and filter candidates by true distance afterwards.
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.
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.
A query filters on two columns and sorts on a third. What index do you create, and what does it cost you?
Put the equality predicates first in a composite index and the ordering column last, so one index serves the filter, the sort and the limit. Every index is a write tax, and whether that tax is a random in-place page update or a sequential append is the difference between a B-tree engine and an LSM engine.
Your dApp backend reads and writes chain state through a hosted RPC provider. How do you make that dependency reliable?
dApp RPC node reliability means treating blockchain nodes as rate-limited, sometimes lagging read replicas rather than perfect infrastructure. Production backends need retries, confirmation depth, bounded log queries, nonce coordination and their own indexed reads.
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.