What actually happens in a database index (and why half of them do nothing)

Same query. Same table. Same million rows. One day it takes 4 seconds. The next day, 4 milliseconds. Nothing changed in the data. The only thing that changed was one line — you added an index.

Four seconds to four milliseconds is a thousand times faster, from one line of SQL. But here's the part nobody tells you: half the indexes people add do nothing. The query stays slow, the writes get slower, and they can't figure out why.

By the end of this you'll know what an index actually is — and the one rule that decides whether yours even gets used.

Prefer to watch? Full walkthrough with the B-tree lookup animation:

With no index: a full table scan

You ask the database for one user by email. With no index, what does it do?

It reads the first row. Not a match. The second row. Not a match. It keeps going — every single row — until it finds yours or runs out. A million rows, a million checks.

SELECT * FROM users
WHERE email = '[email protected]';

With no index, that WHERE line has only one way to run: look at all of them. The work grows with the table — ten times the rows, ten times the wait. That's a full table scan, and that's your four seconds.

What an index actually is

Most people picture an index as a copy of the table, or some kind of cache. It's neither.

An index is a sorted map — just the column you search on, kept in order, with a pointer back to the full row. And the shape it's sorted into has a name: a B-tree (the default index in both Postgres and MySQL — technically a B+ tree).

• At the top, one node — the root.
• It splits into a few branches.
• Each branch splits again, down to the leaves, where the pointers to the rows actually live.

Every node is sorted. The root doesn't hold your data — it holds signposts. Emails before "M"? Go left. "N" and after? Go right. Each step throws away half the tree, or more. You're never reading rows. You're following signs.

The walk: three hops, not a million rows

Watch what the lookup actually does:

• The root — one hop.
• A branch — two.
• A leaf — found.

Three hops. Not a million rows. Three.

That's the whole trick. A scan checks every row — that's the four seconds. A B-tree walk checks a handful — that's the four milliseconds. Grow the table to ten million rows and the scan gets ten times worse, while the tree adds one more hop. That's O(n) versus O(log n).

So why not index everything?

Because an index is not free. It's a second structure the database has to keep in sync. Every time you insert a row, change that column, or delete one, the tree has to be updated too.

Index the whole table, and your reads fly while your writes crawl.

So an index is a trade: faster reads, slower writes, more disk. You pay it on purpose — on the columns you actually search — not on all of them.

The trap: an index your query can't use

Here's the part that trips everyone up. You add the index. The query is still slow. How?

Because an index only helps if your query can actually use it — and it's shockingly easy to write one that can't.

The phone-book rule

Say you index two columns together — last name, then first name:

CREATE INDEX ON users (last_name, first_name);

• Search by last name? Fast.
• Search by last and first? Fast.
• Search by first name alone? Scan.

The tree is sorted by last name first, so first name is useless without it. It's a phone book: perfect for finding "Smith," useless for finding every "John." This isn't a quirk of one database — B-tree indexes everywhere work left to right, or not at all.

Three more ways to quietly kill an index

• Leading wildcard: email LIKE '%dev' starts with a wildcard, so the tree can't seek — it scans.
• Function on the column: wrap it in lower(email) and your index on plain email doesn't match.
• Low selectivity: index a column that's half true, half false, and the database ignores it — a signpost that splits nothing saves nothing.

The takeaway

An index is a sorted B-tree. It turns a full scan into a three-hop walk — but only if your query matches the way it's sorted. The order of the columns isn't a detail. It is the index. Get that order wrong, and you built a tree the query will never climb.

What's the slowest query you've ever fixed with a single index? Drop it in the comments — I read them.

I make Vlad's Stack — how the tools you use every day actually work, for people who write code. Full video walkthrough is above.