Trade-offs of adding indexes to a table

WHAT THIS TESTS The interviewer checks whether you see indexes as a balance between read speed and write cost, rather than a free win. Senior engineers must justify each index against the workload.

A GOOD ANSWER COVERS Indexes are a separate ordered structure, usually a B-Tree, that lets the engine locate rows without scanning the whole table, so selective queries, joins, and ORDER BY get faster. The cost is on the write path: every INSERT, DELETE, and any UPDATE to an indexed column must also insert, delete, or move entries in each affected index, causing write amplification, more I/O, and more lock or latch contention. Indexes also consume disk and buffer-pool memory, competing with data pages for cache. They require ongoing maintenance like rebuilds and statistics updates, and a fragmented or rarely-used index is pure overhead. It is a bad idea to add an index on a column the queries never filter or sort on, on a low-cardinality column where most rows match, or on a small table where a full scan is already cheap, especially when the table is write-heavy.

COMMON WRONG ANSWERS Claiming indexes are universally beneficial. Forgetting that UPDATEs to indexed columns are doubly expensive. Ignoring storage and cache pressure. Believing more indexes always means faster reads, when overlapping indexes just add write cost.

LIKELY FOLLOW-UPS How do you find unused indexes; why is a low-cardinality index weak; what is a composite index column order; when does a covering index justify its size.

ONE CONCRETE EXAMPLE An events table ingests fifty thousand rows per second and someone adds five indexes hoping to speed dashboards. Ingestion throughput halves because each insert now updates six structures, and the dashboards still scan because they filter on an unindexed combination. Dropping three unused indexes and adding one composite index matching the dashboard filter restores write throughput and fixes reads.