Why Your Database Index Gets Ignored (and How to Design One That Isn't)

TL;DR: An index can exist and still do nothing for your query. A multi-column index only serves queries that use its columns from the left, in the index's order. Fix it by putting the column you filter on first. Go further by putting every column the query needs inside the index (a covering index) so the database never touches the table. But every index taxes every write, so design them, don't collect them.

The setup

Last time I showed what happens with no index: the database reads every row. This is the sneakier version.

You added the index. EXPLAIN still says the table is being scanned. The index isn't broken, and the database isn't being dumb. The index just cannot serve that query.

CREATE INDEX idx_name ON users(last_name, first_name);

EXPLAIN SELECT * FROM users WHERE first_name = 'Martha';
-- SCAN users        <- ignored

The left-prefix rule

A multi-column index keeps rows sorted by its first column, then the next: exactly like a phone book, last name then first name.

Search by last name and you jump straight to the page. Search by first name alone and the sorting cannot help you: the Marthas are scattered across every page. There is no way to jump, so the database reads the whole thing.

That is the left-prefix rule: an index serves a query only when the filter starts from the index's first column.

• WHERE last_name = ? -> uses the index
• WHERE last_name = ? AND first_name = ? -> uses the index
• WHERE first_name = ? -> full scan One thing people get wrong: the order of conditions in your SQL means nothing. WHERE a = ? AND b = ? and WHERE b = ? AND a = ? produce the identical plan. Only the column order inside the index counts.

The fix, measured

Rebuild the index so the column you filter on comes first:

CREATE INDEX idx_first_last ON users(first_name, last_name);

On my test table (SQLite, in-memory, 1M rows), that same query flipped from SCAN users at 26.4 ms to SEARCH users USING INDEX at 0.02 ms. Roughly 1,300x. Your absolute numbers will differ; the plan flip is the point.

Covering indexes: never touch the table

A normal index holds just two things: the columns it is sorted on, and a pointer to the row. So after it finds your match, it takes a second hop to the table to grab the other columns you asked for.

But if every column your query needs is already in the index, that second hop disappears. The database answers from the index alone:

-- index: (first_name, last_name, city)
SELECT last_name, city FROM users WHERE first_name = 'Martha';

The plan names it differently per database:

Database	Covering hit looks like
SQLite	USING COVERING INDEX
Postgres	Index Only Scan
MySQL	Extra: Using index

In MySQL/InnoDB the saving is doubled: secondary indexes store the primary key as the row pointer, so the "second hop" is itself another index lookup. (Postgres uses heap tables, so the mechanics differ; Index Only Scan is the thing to look for.)

What every index costs your writes

An index is a B-tree kept in perfect sorted order. That order is what makes reads fast, and it is exactly what writes have to pay for.

Every insert walks down the tree and slots the new value into its exact place. When a node fills up, it splits in two and promotes a key to its parent. That split can ripple upward and grow the whole tree a level. One small insert can rewrite several nodes, and every index on the table is another tree the database keeps balanced on every single write.

When NOT to add an index

• Low-selectivity columns. An order status or a yes/no flag matches half the table. Jumping saves nothing, so the planner scans anyway. The index just sits there taxing writes.
• Write-heavy, read-light tables. If you write far more often than you read, the tax outweighs the benefit.
• Redundant prefixes. An index on (last_name, first_name) already serves last_name queries. Adding another index on last_name alone is write cost for nothing. Index the columns you actually filter, join, and sort on: the ones that narrow to a few rows.

The principle

An index isn't a box you tick. It is a structure you design: the right column order so it gets used, the right columns inside so it covers, and only where reads outweigh writes.

Watch it visually

I made a short, visual breakdown of all of this: the phone book, the real EXPLAIN flip, the covering hit, and the B-tree split animation:

It is episode two of Inside the Database, part of The Leap, a series explaining the systems we build on from first principles. Next up: how loading one page can quietly fire a hundred queries, and nobody notices.

What is the most confusing plan EXPLAIN has ever shown you?