What an index actually does to a slow query

The most common version of this story is someone adds an index, the query gets fast, and nobody ever finds out why. That is fine as long as the index stays. It stops being fine the first time someone adds a WHERE clause, or a second query arrives that the index does not cover, and the fix becomes guesswork.

I would rather explain the mechanism. Here is what actually happens.

The two costs

When a query runs, the engine picks a plan. There are really only two shapes that matter for a normal single-table lookup:

A full scan reads every row and throws most of them away. A B-tree walk descends an ordered structure and arrives at the matching rows, reading roughly the pages between the key you want and the key you just read.

The B-tree walk is O(log n) page reads. A full scan is O(n). On a hundred thousand rows that is the difference between reading the whole table and reading about a dozen pages.

That is the whole reason an index works. The trap is that the second plan is not always more expensive. When a query touches most of the table, the scan is genuinely cheaper, because building and maintaining the index costs something too, and reading a big fraction of rows sequentially is a good way to use a disk. The planner is not wrong when it ignores your index. It is arithmetic.

So the useful question is not "why is my index not being used" but "what fraction of rows does this query need". If it is a tiny slice, you want an index walk. If it is most of them, you want a scan and the index is just write overhead on every insert and update.

Column order is the whole game

Composite indexes are where people get surprised. In a composite index on (a, b, c), the entries are sorted by a, then by b within equal a, then by c within equal b.

That ordering gives you the leftmost prefix rule. An index on (a, b, c) can serve a lookup on a, on a, b, and on a, b, c. It cannot serve a lookup on b or c alone, because those columns are not sorted on their own. They are interleaved with everything else, so there is no contiguous region for the engine to walk.

The mistake I see most is indexing (created_at, status) when the query filters on status and orders by created_at. Written that way the index is nearly useless: the engine cannot seek into it, so it sorts the result afterward anyway.

-- Almost never uses the index. status is not the leading column.
SELECT * FROM posts
WHERE status = 'published'
ORDER BY created_at DESC;

-- Uses it. Filtering comes first, sorting second.
CREATE INDEX idx_posts_status_created ON posts (status, created_at);

The general rule: equality filters first, then the sort or range column. A range condition stops the engine from using anything after it in the key, so if you have WHERE created_at > ?, every column you want after created_at in that index is dead weight.

When the index silently does nothing

Four things turn a correct index into decoration.

A function on the column. WHERE DATE(created_at) = '2026-02-18' cannot use an index on created_at, because the engine has to compute the value per row before it can compare. Rewrite it as a range: created_at >= '2026-02-18' AND created_at < '2026-02-19'. Same answer, and now the index applies.

Type coercion. If the column is VARCHAR and the query passes a number, MySQL converts the column, not the literal, which puts a function on the column again. This one is genuinely nasty because it looks correct and returns correct results.

A leading wildcard. LIKE '%post' cannot seek. LIKE 'post%' can, because the prefix is known. If you need a contains search, that is what a full-text index is for.

Implicit conversion from a bad collation. Two columns joined as utf8mb4_general_ci and utf8mb3_general_ci look identical to a human and look like a type mismatch to the engine.

Covering indexes

There is one more move worth knowing, and it is the cheapest performance win available.

If the index contains every column the query needs, the engine never touches the table at all. It reads the index, finds the rows, and is done.

SELECT id, created_at FROM posts WHERE status = 'published';

-- The engine answers this entirely from the index.
CREATE INDEX idx_posts_covering ON posts (status, id, created_at);

Watch for what this does to writes. Every index is maintained on insert and update. Three indexes on a high-write table means three times the work on every write, for a read speedup you may not need. Adding an index to make one query fast and forgetting that it now taxes every write is how a table ends up with eleven indexes that nobody will admit to adding.

Measure, do not guess

None of the above needs to be taken on faith. Every serious SQL engine will show you the plan.

EXPLAIN ANALYZE
SELECT id, created_at FROM posts WHERE status = 'published';

Read three things. The plan type (Index Scan versus Full Scan versus Index Only Scan). The estimated row count against the actual row count, because a large gap means the statistics are stale and ANALYZE TABLE may be the actual fix. And the time itself, with ANALYZE rather than the plain EXPLAIN, which only estimates.

The last habit is the one that matters. Run the query before you touch anything and keep the number. Without a baseline, "this got faster" is a feeling, and the feeling does not survive the next person to run ALTER TABLE.

On the system I worked on last year, the queries that mattered were all the same shape: a filter, a sort, and a join across a hundred thousand plus rows. Rewriting the predicates so the composite indexes actually applied took the slowest of them down by about 40 percent. No schema change, no cache, no rewrite of the application. Just the order of the columns.