Database indexes.
The index at the back of a book, instead of reading every page to find a word.
Suppose you want every mention of "queueing" in a thick textbook. You could read all 900 pages cover to cover, or you could flip to the index at the back, find the word, and jump straight to the right pages.
A database index is that back-of-the-book index. Without it, finding rows means scanning the whole table. With it, the database keeps a sorted shortcut on the side and jumps straight to what you asked for.
- It’s in here somewhere…1
You want every mention of one word in a 900-page book.
- page 1… page 2… page 3…2
Without an index, the only option is to read every page in order — a full table scan.
- 3
The back-of-the-book index is a sorted shortcut: the word, then the exact pages.
- Page 412. Done.4
Because it’s sorted, you find the word in a handful of steps and jump straight there.
- 5
A database index is the same trick: even a billion rows are only a few dozen steps apart.
- Worth it for the lookups.6
It isn’t free — every write also updates the index, and it takes disk space.
How it jumps so fast
Most indexes are kept as a balanced tree (a B-tree). Searching it is like a guessing game where each step halves what is left: is the value before or after the middle? That means even a billion rows are only a few dozen steps apart.
Because the index is sorted, it also speeds up range questions ("orders from last week") and sorting, not just exact matches.
Why you do not index everything
Every index has to be kept up to date, so each insert or update does a little extra work, and each index takes disk space. Add too many and writes slow down for no benefit.
The skill is indexing the columns you actually search and filter on, in the right order for multi-column lookups. It is one of the highest-leverage fixes for a slow query.