← All articles

Backend & Data

Database indexes: how one line makes a query 1000× faster

2026-06-26 · 6 min read

You've all seen it: a page that loads in 20 milliseconds one day and grinds for 8 seconds once the table grows. Often the fix is a single line — CREATE INDEX — and the speed-up is not 2× but hundreds or thousands of times. The reason is the difference between reading everything and knowing where to look.

Without an index: read every row

Ask a database for WHERE email = 'x@y.com' and, with no index, it has no choice but to check every single row to see which ones match — a "full table scan." At a thousand rows that's instant. At ten million, it's a slog that gets slower every day the table grows.

Full scan versus index seek no index — scan every row match slow, and worse as it grows with index — jump straight there index root a–m n–z row a few hops, no matter the size
An index is a pre-sorted structure (a B-tree). Instead of reading every row, the database narrows the search by half at each hop.

With an index: know where to look

An index is a separate, pre-sorted copy of the column, organised as a tree (a "B-tree"). Finding a value means starting at the top and, at each step, discarding half of what's left — like finding a name in a phone book by opening near the right letter, not reading cover to cover. Ten million rows takes only a couple of dozen hops. That's why the speed-up is so dramatic and only grows with the table.

The gist

No index = read the whole book to find one name. Index = the book is alphabetised, so you flip straight to it. Same answer, wildly different effort.

So why not index everything?

Indexes aren't free:

  • Writes cost more. Every insert or update must also update each index, so over-indexing slows down writes.
  • They take space — an index is extra data stored alongside the table.
  • They only help the right queries. An index on email speeds up lookups by email; it does nothing for a search on name.

The craft is indexing the columns you actually filter, join, or sort on — and no more. Find your slowest query, look at what it filters by, and index that. It's often the single highest-leverage line of code you'll write all week.

DatabasesIndexesPerformanceSQL
← Back to the blog