Database indexes, explained with a phone book

A client’s admin dashboard took eleven seconds to load. The fix was one line, it took ninety seconds to apply, and the page now loads in under a hundred milliseconds. This happens often enough that I have stopped being surprised, and started being curious about why the index was not there in the first place.

The answer is almost always the same. Nobody on the team was quite sure what an index does, so nobody was confident about which one to add.

The phone book

An old phone book is sorted by surname. Finding Hassni takes seconds — open in the middle, decide left or right, repeat. That is a B-tree, which is what nearly every database index actually is.

Now: find everybody with the phone number 555-0199. You read the entire book, cover to cover. That is a full table scan, and it is what your database does on every query without a usable index.

Add a second book, sorted by number, with each entry noting the page in the first book. That is an index: a sorted copy of one column, pointing back at the rows.

Three consequences fall straight out of the picture, and they explain almost everything:

1. An index is only useful in its sorted order. The number book cannot help you find a surname.
2. Indexes cost space and write time. Every new subscriber means updating both books.
3. Sorted order gives you range queries and ordering for free. Everyone between 555-0100 and 555-0200 is a contiguous run of pages.

Composite indexes, and the rule that matters

A composite index sorts by several columns in order — surname, then first name. That book finds all the Hassnis, and Hassni, Azeem specifically. It cannot find everyone called Azeem.

This is the leftmost prefix rule, and it is the single most useful thing to know about indexes.

CREATE INDEX idx_orders ON orders (customer_id, status, created_at);

That index serves:

• WHERE customer_id = 7
• WHERE customer_id = 7 AND status = 'paid'
• WHERE customer_id = 7 AND status = 'paid' ORDER BY created_at DESC

It does not serve:

• WHERE status = 'paid' — skips the leftmost column
• WHERE created_at > ? — same

Which means column order is a design decision, not a formality. The rough guide: equality columns first, then the range or sort column last.

Reading the plan

Stop guessing. Every database will tell you what it did:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 7 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

You are looking for three things.

Scan type. Seq Scan in Postgres or type: ALL in MySQL means it read the whole table. On a large table that is your problem, right there.

Rows examined versus rows returned. Reading 200,000 rows to return 20 is the signal. A good index makes those numbers close.

Filesort or temporary table. Means the database sorted the results after fetching them, because no index provided the order. Extending the index to cover the ORDER BY removes it.

The mistakes I see most

Wrapping the column in a function. The index is on the column, not on the function of it:

WHERE DATE(created_at) = '2026-09-02'        -- index unused
WHERE created_at >= '2026-09-02'
  AND created_at <  '2026-09-03'             -- index used

Same for LOWER(email) = ?. Either store it normalised, or build an expression index on LOWER(email).

Leading wildcards. LIKE '%shirt' cannot use a B-tree, for the same reason you cannot find everyone whose surname ends in “ni”. LIKE 'shirt%' is fine. If you need the first form, you need full-text search or a trigram index, not a B-tree.

Indexing a column with two values. An index on a boolean is_active where 95% of rows are active does nothing — the database will scan anyway, because reading the index and then the rows is more work than just reading the rows. A partial index is the tool here:

CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending';

Small, fast, and only covers the rows you actually query.

Indexing everything. Each index slows every write and consumes space. I have seen tables with fourteen indexes where four were doing all the work. Both MySQL and Postgres can tell you which indexes are never used — check before you add the fifteenth.

Forgetting foreign keys. Postgres does not index the referencing side of a foreign key for you. Every WHERE customer_id = ? and every cascading delete is a full scan until you add it. MySQL’s InnoDB does create one, which is why this bites people moving from MySQL to Postgres.

The covering index trick

If an index contains every column a query needs, the database never touches the table at all:

CREATE INDEX idx_lookup ON orders (customer_id, status, total, created_at);

SELECT status, total, created_at FROM orders WHERE customer_id = 7;

The plan says “Index Only Scan” and the query gets dramatically faster, because the second lookup — index to row — disappears. This is the trick worth remembering for a hot endpoint you cannot otherwise speed up.

Where to start on Monday

Turn on slow query logging with a threshold of 100ms and leave it for a day. You will have a short list, and it will be shorter than you expect — in my experience three or four queries account for most of the pain on any given site.

Take the worst one. Run EXPLAIN ANALYZE. Add the index the plan is asking for. Measure again.

Then do the next one. That is the whole method, and it has never taken me more than an afternoon to make a slow application feel like a different product.