Skip to content

Databases · 1 of 9

Step 1 · The pattern

Find one customer's orders

The app asks for all orders belonging to one customer. With no index, the database reads every row and keeps the ones that match. On a small table that takes no time at all.

  • query
  • answered

In short

Index vs scan: how it works and how it breaks

To find rows matching a condition, a database has two choices. It can read the whole table and check every row, a sequential scan. Or it can use an index: a separate sorted structure, usually a B-tree, that leads straight to the matching rows in a handful of steps. On a small table the difference is invisible, which is how missing indexes reach production. The cost of a scan grows with the table. A query that took a millisecond at launch takes a second a year later, and because each one runs longer, more of them overlap. Eventually they use every CPU and connection the database has, and unrelated queries time out too. Adding an index on the filtered column brings the cost back to almost nothing. Indexes are not free: each one slows writes slightly and takes space. Use EXPLAIN to see which plan the database actually chose.

How this comes up in interviews

"A query that was fast at launch now takes over a second and the whole database is timing out. Nobody changed the query. What is going on?"

Then they ask

  1. How would you confirm which plan the database is using?
  2. Why does one slow query make unrelated queries on other tables time out?
  3. If indexes make reads fast, why not index every column?

A strong answer covers

  • Without an index the database reads every row, and that cost grows with the table.
  • Slower queries overlap, stack up and take every CPU and connection, so unrelated queries time out too.
  • An index is a separate sorted structure, usually a B-tree, that leads to matching rows in a few steps.
  • Each index slows writes slightly and takes space, so index the columns real queries filter on.
  • Use EXPLAIN to see which plan the database chose.

Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.

On one page: the track cheat sheet.

Last updated: Official documentation

Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.