Reading a slow MySQL: four EXPLAIN signals worth acting on

It's a Friday afternoon at a Dutch logistics outfit with thirty trucks and a Symfony app that nobody has rewritten since 2018. The finance dashboard, which used to load in under a second, now takes fourteen. The operations lead has opened a ticket that says, in full, "please add an index." That is a reasonable guess. It is also the wrong place to start.

The right place is EXPLAIN. It is the only honest answer MySQL will give you about what it is actually doing with your query, and most slow queries we get handed at ABN can be triaged in under five minutes by reading four columns of its output. The hard part is knowing which signal tells you to add an index, which tells you to rewrite the query, and which tells you the schema itself is the problem.

The four columns that decide everything

EXPLAIN gives you a dozen columns. Four of them carry almost all the diagnostic weight: type, Extra, rows alongside filtered, and key alongside possible_keys. Read them in that order. Each one points to a different fix, and confusing them is how teams spend a sprint adding indexes that the optimizer then refuses to use.

The MySQL manual is, for once, genuinely useful here. The EXPLAIN output reference is worth keeping in a tab while you work. We will lean on its exact vocabulary below.

Signal one: a full table scan in the type column

The type column tells you how MySQL plans to reach the rows it needs. The hierarchy, from best to worst, runs roughly system, const, eq_ref, ref, range, index, and finally ALL. The last one means a full table scan. On a 5,000-row lookup table that is fine. On a 5-million-row invoices table it is the reason your dashboard is timing out.

EXPLAIN
SELECT id, total_cents, issued_at
FROM invoices
WHERE customer_id = 4821
  AND issued_at >= '2026-01-01';

If that returns type: ALL with no key, you have a missing index. Add a composite index on (customer_id, issued_at), in that order, because the equality predicate comes before the range predicate. Rerun EXPLAIN. You should see type drop to range or ref and the rows estimate fall by orders of magnitude.

Full table scan plus a clear equality or range predicate equals "add an index." Almost any other combination means stop and read the next three signals first.

Signal two: filesort or temporary in the Extra column

The Extra column is where MySQL admits things it would rather you not notice. The two phrases that should make you pause are Using filesort and Using temporary. They do not mean the query is broken. They mean MySQL has to materialise intermediate results in memory or on disk before it can answer you, and that cost scales badly with table size.

Using filesort almost always points at an ORDER BY that no index can satisfy. Using temporary usually shows up with GROUP BY or DISTINCT on columns the optimizer cannot stream through. Neither is fixed by adding a single-column index. They are fixed by rewriting the query or by reordering the columns of an existing composite index so the sort or grouping piggybacks on the index walk.

-- This will filesort even with an index on (customer_id):
SELECT id FROM invoices
WHERE customer_id = 4821
ORDER BY issued_at DESC
LIMIT 20;

-- A composite index on (customer_id, issued_at) lets MySQL
-- walk the index in reverse and skip the sort entirely.

Markus Winand's Use The Index, Luke is the best free reference for this pattern. If your ops team touches SQL once a quarter, send them the sorting and grouping chapters before you send them anything else.

Signal three: examined rows that dwarf rows returned

The third signal is a ratio, not a single value. Look at rows (the optimizer's estimate of how many rows it will examine) next to filtered (the percentage it expects to keep after the WHERE clause). Multiply them and compare to what the query actually returns.

A query that examines 240,000 rows to return 18 is doing thirteen thousand times more work than it needs to. That is rarely an index problem. It is a query shape problem, usually one of three: a join that fans out before it filters, an IN subquery that should be an EXISTS, or a WHERE clause that hides the indexed column inside a function like DATE(created_at) = '2026-06-01', which makes the index unusable.

If the ratio is bad and the query shape is already clean, run ANALYZE TABLE. The optimizer's statistics drift, especially after a large bulk import, and a stale histogram can make it pick a plan that examines ten times more rows than necessary.

Signal four: a missing or surprising key choice

The last pair to read is possible_keys and key. possible_keys lists every index the optimizer considered. key is the one it actually used. Three patterns matter.

First, possible_keys is NULL. No index covers this query at all. Add one, or accept the scan if the table is small and the query is rare.

Second, possible_keys lists three or four indexes and key picks the wrong one. This happens after schema changes, after a big delete, or when histograms are stale. Try ANALYZE TABLE first. If the optimizer still picks badly, FORCE INDEX is a legitimate, if blunt, fix. Leave a comment above the query explaining why.

Third, and the one worth a paragraph of its own: key picked an index but the query is still slow. Look at key_len. If it is shorter than you expect, the optimizer is only using the leading columns of your composite index, usually because a later predicate sits on a column that comes before the one you are filtering on. That is a schema signal. The index needs reordering, or you need a second covering index.

The decision tree, on one page

Put the four signals together and the triage is mechanical:

type=ALL, key=NULL              -> add an index
Extra: Using filesort/temporary -> rewrite query or reorder index
rows examined >> rows returned  -> fix query shape, then ANALYZE
key picked is wrong or short    -> ANALYZE, then FORCE INDEX or rebuild

None of this needs a DBA. It needs reading four columns in order and resisting the urge to add an index before you know which signal you are looking at. The teams who get this wrong are the ones with a migrations/ directory full of add_index_to_x files and a dashboard that is still slow.

When we rebuilt the back office for a Dutch wholesale client last quarter, the slow-query log had eighty distinct queries on it. Sixty came down to two missing composite indexes and one YEAR() call in a WHERE clause. The remaining twenty needed real rewrites, which is the kind of work that goes into our legacy migration engagements. The point is that we did not know which was which until we read EXPLAIN.

The smallest thing you can do today: open your slow-query log, pick the worst offender, and run EXPLAIN on it. If you can name which of the four signals it is firing within sixty seconds, you already know which fix to ship.