A few years into my backend career, a senior engineer looked at a query I’d written, added one line — CREATE INDEX idx_users_email ON users(email); — and a query that took 4 seconds started returning in 12 milliseconds.
I remember feeling two things at once. Relief, because the page finally loaded. And a quiet kind of annoyance, because I didn’t actually understand what had just happened. I just knew “add an index, things get fast” — which is the database equivalent of knowing that turning a key starts a car without knowing anything about the engine.
That gap followed me for years. So this is the explanation I wish someone had given me back then.
The wrong mental model almost everyone starts with
Most people think an index is basically a shortcut list — like the database keeps a separate, smaller copy of your table sorted by whatever column you indexed, and it just checks that instead.
That’s close, but it’s not quite right, and the gap between “close” and “right” is exactly why indexes sometimes don’t help, sometimes slow down your writes, and sometimes get ignored by the database entirely even when you added one.
Quick gut-check before we go further
Say you have a table of 10 million users, and you index the email column. Then you run this:
SELECT * FROM users WHERE email LIKE '%gmail.com';
Will the index help here? Take five seconds before you scroll past this. Most people guess yes. The real answer is no — and by the end of this you’ll know exactly why.
What a table actually looks like without an index
Picture your table as a stack of index cards thrown in a shoebox, in whatever order they happened to be added. No sorting, no structure. When your database runs a query without an index, it does something called a full table scan — it picks up every single card, checks it against your WHERE clause, and puts it back down.
For 100 rows, that’s fine. Nobody notices 100 comparisons. For 10 million rows, that’s 10 million comparisons for a query that might only match one row. That’s your 4-second query.
What an index actually is
Here’s the part most explanations skip. An index isn’t a copy of your data sitting off to the side. It’s a separate data structure — almost always a B-tree — built specifically to answer one question quickly: “where does this value live?”
Think of it less like a shortcut list and more like the index at the back of a thick textbook. The book’s pages aren’t reordered. But the index tells you exactly which page to flip to, so you skip straight there instead of reading the whole book front to back.
A B-tree does this by staying sorted and balanced. Every time you search it, you start at the root, and at each step the tree tells you “go left” or “go right” based on how your value compares to what’s stored there. Instead of checking every row, you’re checking a handful — for 10 million rows, an index typically needs to make around 20–25 comparisons to find your match, not 10 million.
That’s the entire reason your query went from 4 seconds to 12 milliseconds. Not magic. Just a smarter shape for the data.
Try it yourself
If you’ve got a Postgres or MySQL database handy, run this on a decent-sized table:
EXPLAIN ANALYZE SELECT * FROM your_table WHERE some_column = 'some_value';
Look for the words “Seq Scan” (sequential scan — no index used) versus “Index Scan.” Then add an index on that column and run the same query again. Watch the plan change. This is the single best way to actually feel what’s happening instead of just reading about it.
Why your gut-check answer was probably wrong
Back to that LIKE '%gmail.com' query. A B-tree index is sorted, which means it's brilliant at answering "give me everything that starts with G" — but your search pattern started with a wildcard (%). There's no way to binary-search your way to "somewhere in the middle, containing this text." The database can't use the sorted structure at all, so it falls back to scanning everything anyway. Same for functions wrapped around your column, like WHERE LOWER(email) = 'x' — unless you've built a special index for that exact expression, the regular index is invisible to that query.
This is the part nobody warns you about: an index only helps the query shapes it was built for. Slapping an index on a column doesn’t mean every query touching that column gets fast. It means queries that search, sort, or filter on that column in a way the tree can actually use get fast.
The trade-off nobody mentions in the tutorials
Indexes aren’t free. Every time you insert, update, or delete a row, the database also has to update every index on that table to keep it sorted and balanced. A table with one index writes a little slower. A table with eight indexes because someone added one for every column “just in case” writes noticeably slower — sometimes painfully so.
This is why senior engineers don’t just index everything. Every index is a bet: you’re trading slower writes for faster reads on that specific column, in that specific pattern. Good indexing is knowing which bets are worth making.
An index is a separate, sorted structure (usually a B-tree) built to answer “where is this value” fast — not a copy of your table
Without one, the database checks every row, one by one, until it either finds a match or runs out of rows
Indexes only help query patterns they’re built for — leading wildcards and wrapped columns often can’t use them at all
Every index speeds up reads but slows down writes, so adding one is a trade-off, not a free win
