EXPLAIN is the only way to know what MySQL is going to do with your query before it does it — which index it picks, in what order it joins, how many rows it estimates at each step. This article is the operator-grade reading of EXPLAIN we use when a query is slow in production: which columns matter, which patterns are red flags, and the small set of fix patterns that move the needle.
How to verify
The optimizer’s plan changes with statistics, with the MySQL version, and with the literal values in the query. Always reproduce the slow query in the same shape the application sends it.
mysql -e "EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 12345 AND status='paid'\G"
mysql -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345 AND status='paid'\G"
mysql -e "ANALYZE TABLE orders;"
mysql -e "SHOW INDEXES FROM orders;"
mysql -e "SHOW STATUS LIKE 'Handler_read%';"
EXPLAIN ANALYZE (MySQL 8.0.18+) actually runs the query and reports the time spent at each plan node — the gold standard. EXPLAIN FORMAT=JSON shows the cost model’s view without running it.
What’s happening
MySQL’s optimizer chooses a plan by costing every join order, every index, and every access method, and picking the cheapest. The cost model is driven by table statistics (mysql.innodb_index_stats, mysql.innodb_table_stats), so a query that ran fast yesterday can run slow today because stats got stale.
The classic EXPLAIN output has columns that map directly to plan decisions. type is the access method — const and eq_ref are excellent, ref and range are usually fine, index is a full index scan, ALL is a full table scan. key is the index actually chosen. rows is the optimizer’s estimate of how many rows it expects to touch at this step — the most important number on the page. Extra carries the warnings: Using filesort means a sort the index could not provide, Using temporary means a temporary table on disk, Using where means rows are filtered after fetch (cheaper if the filter is on the index).
EXPLAIN ANALYZE adds the actual numbers. The pattern to look for is “rows estimated 100, actual 1000000” — that estimation error is what makes the optimizer choose a bad plan. The fix is usually ANALYZE TABLE (or a histogram), not a query rewrite.
The procedure
-
Capture the slow query exactly as the application runs it, including literal values. The plan for
WHERE customer_id = 1can be totally different fromWHERE customer_id = 999999if value 1 has a million rows and 999999 has none.EXPLAIN FORMAT=JSON SELECT o.id, o.total, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid' AND o.created_at >= '2026-05-01' ORDER BY o.created_at DESC LIMIT 50\G -
Identify the most expensive node. In JSON output, find the highest
query_costor the deepest scan; in tabular output, the highestrowsvalue.EXPLAIN ANALYZE SELECT o.id, o.total, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid' AND o.created_at >= '2026-05-01' ORDER BY o.created_at DESC LIMIT 50\GLook for
actual time=...androws=...per node. A scan onordersshowingrows=10000000 actual=10000000is reading the whole table. -
Compare what the optimizer expected vs what happened. A 10x estimation error usually means stale stats.
ANALYZE TABLE orders; ANALYZE TABLE customers;Re-run
EXPLAIN ANALYZE. If the plan changes and the time drops, the only fix was stats. -
If the plan is still bad, look at what index is being used vs what should be.
SHOW INDEXES FROM orderslists every index with cardinality.SHOW INDEXES FROM orders;A composite index on
(status, created_at)will serve both the equality predicate and the range filter, and theORDER BY created_at DESC LIMIT 50reads only 50 rows in reverse index order.ALTER TABLE orders ADD INDEX idx_status_created (status, created_at); -
For
Using filesortandUsing temporary, the fix is almost always an index that delivers rows already in the required order. ForUsing filesorton aGROUP BY, an index on the grouping columns avoids the sort. -
For
type=ALLwith aWHEREclause, the optimizer either has no usable index, has chosen to ignore one because the predicate matches most rows, or the column is wrapped in a function (WHERE DATE(created_at) = ...) that defeats the index. Rewrite asWHERE created_at >= ... AND created_at < .... -
When the optimizer picks the wrong index despite a better one existing, force its hand with
USE INDEXorFORCE INDEX, and report it. Optimizer hints in MySQL 8 (/*+ INDEX(orders idx_status_created) */) are the modern way; they survive query rewrites better than the oldUSE INDEXsyntax.
Common pitfalls
SELECT *against a wide table — pulls every column over the wire and forbids index-only scans. Project to the columns you need; letUsing index(covering index) become possible.WHERE function(column) = literal— wraps the column in a function and skips the index.LOWER(email),DATE(created_at),JSON_EXTRACT(json_col, '$.id')all do this. Either expression-index it, or store the result in a generated column with an index.- Composite index column order —
(a, b, c)servesWHERE a=...,WHERE a=... AND b=..., andWHERE a=... AND b=... AND c=..., but notWHERE b=...alone. The leftmost prefix rule decides what is usable. - Confusing
rowswith rows read —rowsinEXPLAINis an estimate of rows at that plan node, not rows returned by the whole query. A plan withrows=10000000at the leaf andLIMIT 50at the root still touches ten million rows. - Ignoring
Using filesortbecause the result is fast — filesort that fits in memory (sort_buffer_size) is OK; filesort that spills to/tmpis the slow tail nobody catches until production.
EXPLAIN turns a slow-query ticket from a guessing game into a numbered list of decisions to question. In the engagements we run, query optimization is part of our weekly database review — we mine pt-query-digest and events_statements_summary_by_digest, run EXPLAIN ANALYZE on the top offenders, propose indexes or query rewrites, and apply them in a controlled rollout. For the broader pattern of operating MySQL with this discipline, see /en/services/managed-operations/.