Skip to content
BDOT SOFTWAREBDOT Software

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

MySQL EXPLAIN query plan showing index access and a filesort on a slow orders query

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.

  • type is ALL: a full table scan. No usable index exists, or the WHERE clause cannot use the ones that do. Fix this first; it is usually the whole fix.
  • key is NULL while possible_keys is not: MySQL considered an index and rejected it. Common causes: a function on the indexed column, a type mismatch, or an OR across two columns with separate indexes.
  • rows is far larger than the result: the query examines thousands of rows to return a handful. A needed column is missing from the index.
  • Extra says Using filesort or Using temporary: MySQL sorts or groups rows it fetched first. An index matching the ORDER BY or GROUP BY removes the sort.
  • filtered is 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 EXPLAIN on production-sized data and read type, key, rows, and Extra first.
  • Fix the biggest number: add or adjust one index, or rewrite one clause. Change one thing and re-run EXPLAIN to 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.