What a database index is and when it helps

WHAT THIS TESTS The interviewer checks foundational understanding of why indexes accelerate reads and what they cost.

A GOOD ANSWER COVERS An index is a separate data structure, most commonly a B-tree, that stores indexed column values in sorted order along with pointers back to the table rows. Without an index, finding rows matching a condition requires a full table scan, reading every row. With an index, the engine navigates the sorted structure in logarithmic time to locate matching rows directly, like using a book's index instead of reading every page. Indexes most help highly selective WHERE filters, join columns, and ORDER BY or range queries on the indexed column. The trade-off is real: every insert, update, or delete must also maintain the index, slowing writes, and the index consumes additional storage. So you index columns that are frequently queried and selective, not every column.

COMMON WRONG ANSWERS Saying you should index every column for speed, which bloats storage and cripples write performance. Believing indexes help low-selectivity columns like a boolean flag, where a scan is often as fast. Forgetting that a query may still ignore an index if it returns most of the table.

LIKELY FOLLOW-UPS What is selectivity and why does it matter? When does the optimizer skip an index? What is a covering index? How do composite-index column order and clustered indexes work?

ONE CONCRETE EXAMPLE A users table with ten million rows is queried by email on every login. Without an index, each login scans ten million rows. Adding an index on email lets the engine find the one matching user in a few steps, turning a slow scan into a near-instant lookup.