A database table is, underneath, just a pile of rows. When you ask it to find every customer named "Patel" or every order placed yesterday, it has to figure out where those rows actually are. How it does that — quickly, or painfully slowly — comes down to whether the right index exists.
Indexing is one of the highest-leverage ideas in working with data: the same query can take milliseconds or minutes depending on whether the database can jump straight to the answer or has to hunt for it.
The problem: the full table scan
Imagine a users table with ten million rows and you run SELECT * FROM users WHERE email = 'amy@example.com'. Without any help, the database has no idea where Amy's row lives. So it does the only thing it can: it reads every single row, top to bottom, checking each one's email against what you asked for.
This is called a full table scan. It's the brute-force approach — correct, but slow. Its cost grows linearly with the size of the table: this is O(n) work, meaning if the table doubles, the query takes roughly twice as long.
On a tiny table you'd never notice. But tables grow, and a scan that was instant at a thousand rows becomes a real problem at ten million. Step through a scan of a small, 16-row table below and count the rows it reads. When it reaches Amy, predict whether it can stop.
A full table scan is the default, not an error. If you query a column that has no index, the database silently falls back to scanning the entire table — it still returns the right answer, so nothing looks broken. The query just gets slower and slower as the table grows, and you often don't notice until production traffic makes it hurt.
The solution: an index
An index is a separate, sorted lookup structure that the database keeps alongside the table. Think of the index at the back of a textbook: instead of flipping through every page to find "photosynthesis," you go to the back, find the word in alphabetical order, and it tells you exactly which pages to turn to.
A database index works the same way. It maps each value of a column (like email) to the location of the rows that hold it. Because the entries are kept in sorted order, the database can find any value without reading everything.
Most indexes are built as a B-tree — a balanced tree structure that stays shallow even when it holds millions of entries. To find a value, the database starts at the top and follows a handful of branches down to the right leaf, discarding huge chunks of the data at every step. The trick is that each node is a whole disk page holding hundreds of sorted keys, so every step divides the search by hundreds, not by two. Before you step through the descent below, guess how many pages a lookup reads in a million-row table.
This is the big win. A lookup through a B-tree index is roughly O(log n) — the work grows with the logarithm of the table size, not the size itself. Doubling the table adds barely any cost. Concretely, finding one row among ten million reads 3 or 4 index pages plus the row itself, instead of ten million rows, turning a slow scan into a near-instant lookup. That's a dramatic latency improvement for reads.
Your users table grows from 1 million to 100 million rows. Roughly how does a lookup by an indexed email change?
Indexes aren't free
If indexes only made things faster, you'd put one on every column and be done. The catch is the cost on the write side. An index is extra data, so it takes additional storage on disk — sometimes a lot of it for a big table.
More importantly, the index has to stay in sync with the table. Every time you insert, update, or delete a row, the database must also update every index that covers the affected columns, keeping each one correctly sorted. So indexes make reads faster but make writes slower — a classic trade-off. Step through one INSERT into a table with five indexes, and predict how many writes it makes before it returns.
Don't index everything. Each index you add speeds up some reads but slows down every write and consumes storage. A table buried under redundant indexes can write more slowly than one with none — and the planner may not even use most of them. Add indexes deliberately, in response to real query patterns, not just in case.
When to add an index
The rule of thumb is to index the columns the database actually searches by. In practice that means columns used in:
WHEREclauses — the columns you filter on, likeemailorstatus.JOINconditions — the keys you join tables on, so the database can match rows quickly.ORDER BYclauses — because a sorted index can hand back rows already in order, skipping a separate sort step.
Columns that never appear in any of these don't benefit from an index, so leave them alone.
Appearing in a WHERE clause isn't enough on its own, though. An index pays off when it narrows the search to a small slice of the table, which is called its selectivity. An index on email, with one row per value, is highly selective. An index on status, where 90% of users are 'active', is not: it points at most of the table, and following it means fetching rows one at a time, scattered all over the disk. Predict whether that index helps, then switch the filter to email to compare.
A low-selectivity index is mostly cost. For a value that matches a large share of the table, the query planner will usually ignore the index and scan, so you pay on every write and gain nothing on reads. The exception is the rare value: if you often query status = 'banned' and only 0.1% of users are banned, an index can still pay off, and many databases let you build a partial (or filtered) index that covers only those rows.
You can also build a composite index that covers several columns at once — for example, an index on (last_name, first_name). This is ideal when your queries filter or sort on those columns together. The order matters: a composite index on (last_name, first_name) helps queries that filter by last name (or by last name and first name), but not ones that filter by first name alone, much like a phone book sorted by surname.
Indexing is one of two complementary tools for making reads fast. An index speeds up how the database finds data on disk; caching avoids touching the database at all by keeping hot results in fast memory. They solve the same problem — slow reads — at different layers, and real systems often lean on both: a well-chosen index for the queries that reach the database, and a cache in front of the ones that run again and again.
A report filters orders with WHERE status = 'shipped', and 95% of orders are shipped. A teammate proposes adding an index on status to speed it up. What's the most likely result?