How Database Indexes Work
Imagine looking for a single word in a 900-page book with no table of contents and no index — you'd start on page one and read until you found it. A database without an index works exactly like that. Add an index, and suddenly the database can jump straight to the answer. Here's why that one idea makes queries hundreds or thousands of times faster.
The slow way: reading every row
When you ask a database for a row — say, the customer whose email is
sam@example.com — its simplest option is a full table scan. It starts at
the first row and checks each one in turn until it finds a match (or reaches the
end). If the table has a thousand rows, that's fine. If it has ten million, the
database is doing ten million comparisons for a question with one answer.
The work grows in lockstep with the table. Double the data, double the time. That's the cost of having no shortcut: the only way to be sure a value isn't there is to look everywhere.
What an index actually is
An index is a separate, sorted copy of one column (or a few), kept alongside the table and pointing back to the full rows. The classic structure behind it is a B-tree — a shallow, branching tree that stays balanced as data changes.
Think of it like the index at the back of a textbook. The entries are in alphabetical order, and each one tells you the page to turn to. You never read the whole book to find "photosynthesis"; you flip to the P section, run your finger down, and jump straight to page 412. The database does the same thing: follow the branches of the tree, narrowing the range at each step, until you land on the exact entry — which then points to the real row.
Why it's so much faster
The magic is in how the search shrinks. Each step down a B-tree cuts the remaining possibilities by a large factor, so the number of steps grows very slowly even as the table explodes in size. Going from a thousand rows to a million doesn't make the lookup a thousand times slower — it adds just a handful of extra steps.
That's the difference between checking every row and taking a few decisive hops. A query that scanned millions of rows can become one that touches a dozen. The data got bigger; the search barely noticed.
Indexes aren't free
If indexes are so good, why not put one on every column? Because they have a cost. Each index is extra data the database must store, and — more importantly — every time you insert, update, or delete a row, the database has to keep every relevant index sorted and correct. A table drowning in indexes makes reads fast but writes slow, and eats disk space doing it.
Indexes also only help when a query can use them. Search for an exact email or a range of dates and the index shines. But ask for every row containing a substring in the middle of a text field, and the sorted order gives no shortcut — the database falls back to scanning. Choosing indexes is about matching them to the questions you actually ask most.
The takeaway
An index trades a little write speed and storage for an enormous gain in read speed, by keeping a sorted map that lets the database skip instead of scan. The lesson generalizes far beyond databases: when you'll search the same data again and again, it pays to organize it first. That's why books have indexes, libraries have catalogues, and every serious database gives you the tools to build your own.
The next time a query returns instantly from a table with millions of rows, you'll know what's happening under the hood — a quiet flip to exactly the right page.
This explainer was made with LineLapse — type a topic, get a hand-drawn video. Make your own →