Query Optimization: Reading an EXPLAIN Plan

You met EXPLAIN briefly in the indexes lesson. Now let's actually read one properly, because understanding its output is the real skill behind diagnosing a slow query, not just knowing the keyword exists.

Run EXPLAIN in front of any SELECT, and instead of running the query, MySQL tells you how it plans to run it:

EXPLAIN SELECT customers.name, orders.total
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE orders.total > 500;

Reading The Explain Columns

The output is a table with several columns worth knowing. type describes the access method MySQL will use per table: ALL means a full table scan (checking every row, generally bad on a large table), while ref, eq_ref, or range mean it's using an index to narrow things down efficiently (generally good). Seeing ALL on a large table where you expected an index to be used is usually your first clue something's wrong.

What Rows And Key Tell You

rows is MySQL's own estimate of how many rows it will need to examine for that part of the query. This is an estimate, not an exact count, based on statistics MySQL keeps about the table, but it's genuinely useful for comparing before and after adding an index: if rows drops from 500,000 to 12 after adding an index, that index is clearly doing real work.

key tells you which index, if any, MySQL actually chose to use for that table in this query, exactly as you saw in the indexes lesson. Sometimes an index exists but MySQL's optimizer decides not to use it, because its own statistics suggest a full scan would actually be cheaper, for example, on a very small table, or when a WHERE condition would match most of the table's rows anyway (filtering out "most rows" barely helps, so the overhead of consulting an index may not pay for itself).

Reading The Extra Column

Extra often contains important hints. "Using filesort" means MySQL had to sort results manually after fetching them, usually because the ORDER BY column isn't indexed in a way that already returns rows pre-sorted, this can be slow on large result sets. "Using temporary" means MySQL had to build a temporary table internally to finish processing the query, commonly seen with certain GROUP BY or DISTINCT patterns on large data, and worth investigating if you see it on a query that feels unexpectedly slow.

A genuinely common beginner mistake is looking only at type and assuming "ALL is always bad, anything else is always fine," without context. A full table scan (ALL) on a tiny lookup table with 20 rows is completely harmless, genuinely faster in some cases than the overhead of consulting an index. The real question is always: is the chosen plan appropriate for this table's actual size and how often this query runs. EXPLAIN gives you facts; judgment turns those facts into a decision.

Explain Analyze For Real Timings

For a deeper, more honest picture than plain EXPLAIN, EXPLAIN ANALYZE (available on modern MySQL versions) actually runs the query and reports real measured timings alongside the plan, rather than only estimates:

EXPLAIN ANALYZE SELECT customers.name, orders.total
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE orders.total > 500;

This is more expensive to run (it genuinely executes the query), but when a query's real-world slowness doesn't match what plain EXPLAIN's estimates suggested, EXPLAIN ANALYZE is the tool that tells you where actual time is being spent.

A practical workflow worth adopting: when a query feels slow, run EXPLAIN first and look for ALL on a large table, or Using filesort/Using temporary in Extra. Try adding or adjusting an index based on what you find, then re-run EXPLAIN and compare the rows estimate and type before and after, confirming your change actually helped rather than just assuming it did.

Try It Yourself

To make this stick, try this: take a query you wrote in an earlier lesson's exercise involving a JOIN and a WHERE clause, run EXPLAIN on it, and write down in plain English what type, rows, and key are each telling you about how MySQL plans to execute it.