Insights / Databases
MySQL EXPLAIN: how to read a query plan and fix the slow query
A slow query log names the slow query; EXPLAIN tells you why. How to read a MySQL query plan — type, key, rows, Extra — and fix the slow query with the right index.
Kiran Bandarupalli · 19 Nov 2026 · 5 min read

A slow query log tells you which query is slow. It does not tell you why. That is the job of EXPLAIN: ask MySQL how it plans to run a query before it runs it, and the plan usually names the problem in one line. A five-second query is rarely slow because the database is slow. Usually it reads far more rows than it needs, sorts rows it should never have fetched, or joins in the wrong order. The MySQL slow query log shows you where the time goes; this post covers what to do about the worst entry on that list.
What EXPLAIN actually shows
Prefix any SELECT with EXPLAIN and MySQL returns a plan instead of results: one row per table. Four columns carry most of the answer: type, key, rows, and Extra.
The type column is the access method: const and eq_ref return one row by key, ref a few rows by a non-unique index, range a slice of an index, index a full index scan, and ALL a full table scan. A type of ALL on a table with more than a few hundred rows is the most common cause of a slow query, and the easiest to fix. The key column names the index MySQL chose, or NULL when it chose none; possible_keys lists the ones it considered. The rows column is MySQL's estimate of how many rows it will examine, and filtered the estimated percentage surviving the WHERE clause. Treat rows as an estimate from table statistics, not a measurement; stale statistics make it wrong in both directions.
Reading a real plan
Take a query every business application has: the most recent paid orders for one customer.
EXPLAIN SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
On a table with 400,000 orders and an index only on customer_id, the plan looks like this:
id select_type table type possible_keys key key_len rows Extra
1 SIMPLE orders ref idx_customer idx_customer 4 11800 Using where; Using filesort
The access type is ref: MySQL uses the customer index and expects about 11,800 rows. But Extra contains Using filesort, so MySQL fetches and sorts all 11,800 rows for the ORDER BY, only to throw away all but 20. The query returns 20 rows and does the work of 11,800. Rows examined versus rows returned decides whether a query is fast.
The fix is a composite index covering the query shape: (customer_id, status, created_at DESC). MySQL walks the index in order and stops after 20 rows. The plan changes to rows: 20 with no Using filesort. Nothing else changed.
The five things to check first
Most slow queries fail on one of these.
typeisALL: a full table scan. No usable index exists, or theWHEREclause cannot use the ones that do. Fix this first; it is usually the whole fix.keyisNULLwhilepossible_keysis not: MySQL considered an index and rejected it. Common causes: a function on the indexed column, a type mismatch, or anORacross two columns with separate indexes.rowsis far larger than the result: the query examines thousands of rows to return a handful. A needed column is missing from the index.ExtrasaysUsing filesortorUsing temporary: MySQL sorts or groups rows it fetched first. An index matching theORDER BYorGROUP BYremoves the sort.filteredis very low: few examined rows survive the filter. The filter column belongs in the index.
Fixes, in the order to try them
Start with the index: it is usually the answer and the cheapest change. A composite index follows the leftmost-prefix rule: it serves a query filtering on its first column or first two, but not one filtering only on the second. Put the most selective equality columns first, the range or ordering column last. An index containing every column the query selects is a covering index: MySQL never touches the table.
If the index is right and the query is still slow, rewrite it. The usual culprits are a function on an indexed column, such as WHERE DATE(created_at) = '2026-11-01', which blocks index use where a range comparison uses it; SELECT * on a wide table, which reads columns the application never uses and defeats a covering index; and OR conditions across different columns, which MySQL often handles by scanning instead of merging two index lookups.
For joins, check the order MySQL chose: the first table listed is the driving table. Usually the fix is a missing index on the join column, not a forced join order. MySQL 8's EXPLAIN ANALYZE runs the query and reports real row counts per step, so you can compare the estimate against reality.
When the plan lies
After a large import, a bulk delete, or a schema change, run ANALYZE TABLE and re-check the plan: stale statistics are the usual reason a plan changes. The second trap is testing on a development database with a thousand rows. A query scanning a thousand rows in development scans four hundred thousand in production, and EXPLAIN on a small table reports type: ALL with a tiny row count and looks harmless. Always explain against production-sized data. The third trap is type coercion: comparing an integer column to a quoted string can silently disable an index. If possible_keys lists an index that key ignores, check the types on both sides of every comparison. And a query slow only under load may not be a query problem: MySQL connection pooling explains why opening a fresh connection per request makes even a fast query look slow.
A repeatable workflow
Run this whenever a page slows down.
- Find the query: take the worst entry from the slow query log and copy the exact SQL.
- Explain it: run
EXPLAINon production-sized data and readtype,key,rows, andExtrafirst. - Fix the biggest number: add or adjust one index, or rewrite one clause. Change one thing and re-run
EXPLAINto confirm the plan improved. - Confirm with
EXPLAIN ANALYZE: verify the real row counts match the estimate. - Ship it like a code change: an index added in production is a schema change. Zero-downtime database migrations covers adding one without locking the table, and MySQL backups with point-in-time recovery is the safety net.
- Watch it for a week: check the slow query log after the next traffic peak. A fix that works at noon can fail at the evening rush.
Most slow queries are not hard problems: a missing index, a function on an indexed column, or a sort the database was never asked to avoid. EXPLAIN shows you which one it is in about ten seconds. Reach for it before adding a cache, scaling the server, or rewriting the feature. If your application has a page that has been slow for months and nobody has explained its queries yet, talk to us — our cloud, VPS and DevOps team and custom software team find and fix these in production systems every week, usually in the same visit that finds them.