Skip to content

MySQL query optimization — reading EXPLAIN in production

How an MSP reads EXPLAIN and EXPLAIN ANALYZE in production — join order, access type, key choice, rows estimated vs actual, and the fix patterns we apply most.

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

  1. Capture the slow query exactly as the application runs it, including literal values. The plan for WHERE customer_id = 1 can be totally different from WHERE customer_id = 999999 if 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
  2. Identify the most expensive node. In JSON output, find the highest query_cost or the deepest scan; in tabular output, the highest rows value.

    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\G

    Look for actual time=... and rows=... per node. A scan on orders showing rows=10000000 actual=10000000 is reading the whole table.

  3. 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.

  4. If the plan is still bad, look at what index is being used vs what should be. SHOW INDEXES FROM orders lists 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 the ORDER BY created_at DESC LIMIT 50 reads only 50 rows in reverse index order.

    ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
  5. For Using filesort and Using temporary, the fix is almost always an index that delivers rows already in the required order. For Using filesort on a GROUP BY, an index on the grouping columns avoids the sort.

  6. For type=ALL with a WHERE clause, 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 as WHERE created_at >= ... AND created_at < ....

  7. When the optimizer picks the wrong index despite a better one existing, force its hand with USE INDEX or FORCE 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 old USE INDEX syntax.

Common pitfalls

  • SELECT * against a wide table — pulls every column over the wire and forbids index-only scans. Project to the columns you need; let Using 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) serves WHERE a=..., WHERE a=... AND b=..., and WHERE a=... AND b=... AND c=..., but not WHERE b=... alone. The leftmost prefix rule decides what is usable.
  • Confusing rows with rows read — rows in EXPLAIN is an estimate of rows at that plan node, not rows returned by the whole query. A plan with rows=10000000 at the leaf and LIMIT 50 at the root still touches ten million rows.
  • Ignoring Using filesort because the result is fast — filesort that fits in memory (sort_buffer_size) is OK; filesort that spills to /tmp is 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/.