Most developers learn indexes as a magic spell. Query slow? Add an index. Query fast. Nobody asks why.

Then one day the spell stops working. The index is there, EXPLAIN says it isn't being used, and the query is still scanning the whole table. Or you add indexes everywhere "just in case" and your imports get slower every month.

The fix for both is the same: be able to picture what an index actually is. It takes about five minutes. Let's go minute by minute.

Minute 1: the phone book

Imagine a phone book with a million names, printed in random order. To find "Shiroz, Mohammed" you'd have to read every single entry. That's a full table scan, and it's what your database does when there's no useful index.

Now imagine the same phone book sorted by surname. You'd open it near the middle, see "M", flip forward, see "R", flip a little further, and land on "S" in a handful of moves. You never read most of the book.

That's all an index is: a separate, sorted copy of one or more columns, with a pointer back to the full row. The table stays as it is. The index is the sorted list you search instead.

Minute 2: why it's a tree, not a list

A sorted list is great for reading, but databases live on disk, and they read in fixed-size chunks called pages (16 KB by default in MySQL's InnoDB). Jumping around a giant sorted list would still mean reading lots of pages.

So the sorted data is organised as a B-tree (strictly, InnoDB uses a variant called a B+tree). Picture three levels:

                 [ root:  G | P ]
                /        |        \
     [ A..F ]       [ G..O ]       [ P..Z ]       <- branch pages
      /  |  \        /  |  \        /  |  \
   leaf pages: sorted keys + pointer to each row

The root page says "names before G go left, G to O go middle, P onwards go right". Each branch page narrows it down again. The leaf pages hold the actual sorted keys.

The trick is that each page holds hundreds of keys, so the tree is very wide and very short. Three or four levels can cover millions of rows. Finding any single value means reading one page per level: a few page reads instead of the whole table. And the root and upper levels are used constantly, so they're almost always already in memory.

Minute 3: what "pointer to the row" really means

In InnoDB, the table itself is stored as a B-tree ordered by the primary key. This is called the clustered index: the leaf pages of the primary key are the rows.

Every other index (a secondary index) stores the indexed columns plus the primary key value. So a lookup by email is really two lookups:

1. Walk the email index to find the matching entry and its primary key.
2. Walk the primary key tree to fetch the full row.

For one row, that's nothing. For thousands of rows, that second hop happens thousands of times, and it's often why "it uses the index, but it's still slow".

Minute 4: composite and covering indexes

A phone book is really sorted by surname, then first name. That's a composite index:

CREATE INDEX idx_payments_tenant_date
    ON payments (tenant_id, paid_at);

Like a phone book, it's only useful from the left. It helps with:

• WHERE tenant_id = 42
• WHERE tenant_id = 42 AND paid_at >= '2025-01-01'
• WHERE tenant_id = 42 ORDER BY paid_at (already sorted, no extra sort step)

It does not help with WHERE paid_at >= '2025-01-01' alone. Finding everyone whose first name is "Sara" in a phone book sorted by surname means reading the whole book. This is the leftmost prefix rule, and it's the reason column order in a composite index matters so much: put the column you filter by equality first, and the range or sort column after it.

Now the best trick in the book. If an index contains every column the query needs, the database can answer from the index alone and skip the second hop entirely. That's a covering index:

-- Answered entirely from idx_payments_tenant_date:
SELECT paid_at FROM payments
WHERE tenant_id = 42
ORDER BY paid_at DESC;

(Remember the primary key is stored in every secondary index, so selecting id is free too.) In MySQL's EXPLAIN, look for Using index in the Extra column. That's your sign the query never touched the table.

Minute 5: the price you pay

Indexes aren't free. Every index is another sorted structure that must be kept sorted.

• Every write updates every index. An insert into a table with six indexes is really seven inserts. Updates to an indexed column mean removing and re-inserting index entries.
• They take space, on disk and in the memory buffer that keeps hot pages cached.
• Random keys hurt. Inserting random values (like random UUIDs as a primary key) lands new entries all over the tree, causing page splits and scattered writes. Sequential keys just append to the right-hand edge.
• Low-selectivity columns rarely help. An index on a status column with three values often isn't worth it on its own, because each value matches a huge share of the table anyway. As part of a composite index, it can be very useful.

Bonus: why your index is being ignored

With the tree in your head, the classic "index not used" cases suddenly make sense. The database can only walk the tree if it can compare your value against the sorted values as stored:

• WHERE YEAR(created_at) = 2025 wraps the column in a function, so the sorted order can't be used. Write created_at >= '2025-01-01' AND created_at < '2026-01-01' instead.
• WHERE email LIKE '%@gmail.com' has a leading wildcard. Like searching a phone book for surnames ending in "-son".
• Comparing a string column to a number forces a conversion on every row.
• Skipping the first column of a composite index, as we saw in minute four.

The five-minute summary

• An index is a sorted copy of some columns, stored as a short, wide B-tree.
• Lookups are fast because you read one page per level, not the whole table.
• Secondary indexes point to the primary key, so many matches mean many extra hops.
• Composite indexes work from the left. Covering indexes skip the table entirely.
• Every index makes writes a little slower. Add them on purpose.

What's the index that made the biggest difference in a project of yours, and what was the query it fixed?