How to read a slow MySQL query plan

A slow query is rarely mysterious once you stop guessing at it. MySQL will tell you exactly what it intends to do. The plan is just written in a format that rewards knowing which three columns to read first.

Find the right query first

Optimising the query somebody complained about is usually optimising the wrong one.

Turn the slow log on, let it collect, then rank by total time rather than by worst single run.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;          -- seconds; tune to your traffic
SET GLOBAL log_queries_not_using_indexes = 'ON';

A query taking 80ms ten thousand times an hour costs far more than a nine-second report that runs once at 3am, and only one of those two gets complained about. pt-query-digest over the log sorts by total and is almost always pointing at the real work.

Three columns, in this order

EXPLAIN SELECT id, status, created_at
          FROM lead
         WHERE source = 'web'
           AND created_at >= '2026-10-01'
         ORDER BY created_at DESC
         LIMIT 20;

id  select_type  table  type   possible_keys  key   rows      Extra
 1  SIMPLE       lead   ALL    NULL           NULL  2418330   Using where; Using filesort

Read rows first. It is how many rows MySQL expects to examine for this step, and two million examined to return twenty is the finding. Everything else on that line is an explanation of why.

Then type, which describes how the table is reached, best to worst:

ALL on a large table is the single most common cause of a slow query, and it is right there in the second column.

Then key. NULL means no index was used. If possible_keys lists candidates but key is NULL, the optimiser looked at them and decided a scan would be cheaper, which is sometimes correct and sometimes a symptom of stale statistics.

Extra holds the remaining clues. Using filesort means the result had to be sorted afterwards. Using temporary means a temporary table was built. Using index is the good one: the index answered the query without touching the rows at all.

Three patterns explain most of them

No index for the filter. The plan above wants source and created_at together.

ALTER TABLE lead ADD INDEX idx_source_created (source, created_at);

Column order is the whole trick, and it is where most composite indexes go wrong. MySQL uses one left to right, so (source, created_at) serves a query filtering on source, or on source and created_at together, but does nothing for one filtering on created_at alone. Equality column first, range column second, and the same index then satisfies the ORDER BY as well, which removes the filesort for free.

A function wrapping the column. This cannot use an index on created_at:

WHERE DATE(created_at) = '2026-10-01'        -- index unusable

WHERE created_at >= '2026-10-01'             -- index usable
  AND created_at <  '2026-10-02'

Implicit conversions do the same damage less visibly. Comparing a VARCHAR column to a number makes MySQL convert every row before comparing, which is a full scan wearing a disguise.

Deep pagination. LIMIT 20 OFFSET 100000 reads 100,020 rows and discards 100,000 of them, and it gets worse the further anyone pages.

SELECT id, status FROM lead
 WHERE source = 'web' AND created_at < :last_seen_created_at
 ORDER BY created_at DESC
 LIMIT 20;

Keyset pagination carries the last row's sort value forward rather than counting from the start, so the cost stops depending on how deep the user has gone.

When the plan looks fine and it is still slow

Switch from intention to measurement.

EXPLAIN ANALYZE SELECT ...;

That runs the query and reports actual rows and actual time per step. The signal you want is disagreement: if the plan estimated 200 rows and the measurement says 400,000, the optimiser chose its plan from bad information. ANALYZE TABLE lead; refreshes the statistics, and the plan frequently changes on its own afterwards without you touching the query.

Do all of this against a realistic copy of the data. An index that looks decisive against ten thousand test rows can be ignored entirely against ten million, because the optimiser's choice depends on how selective the condition actually is. That makes a safe way to copy production to a laptop a prerequisite for this work rather than a convenience.

The whole method

It is short enough to keep in your head, which is most of why it works.

Rank by total time. Read rows, then type, then key. Fix the one pattern the plan is pointing at.

Most of the time it is a missing or badly ordered composite index, a function applied to an indexed column, or pagination counting from the beginning. All three are visible in the plan before you change a line, and all three stay invisible for as long as you optimise by intuition.