TuningLens

MySQL guide · Published 24 September 2026 · Updated 27 September 2026

How to read a MySQL EXPLAIN plan

MySQL EXPLAIN shows the optimizer’s chosen execution plan. Read it to understand table access, candidate indexes, join order, and estimated work; then compare estimates with measured behavior before changing production.

Direct answer: Start with the rows in the order MySQL plans to access them. For each table, inspect type, possible_keys, key, rows, and Extra. These are plan details and estimates, not proof of elapsed runtime. Use representative data and, where safe, EXPLAIN ANALYZE to compare estimates with actual rows.

Scope and versions

This guide covers MySQL Community Server 8.0 and 8.4, typically using InnoDB. Output columns and available formats can differ by version and storage engine. Confirm behavior against the exact server version; MariaDB and other compatible products have their own differences.

Collect a plan without running the SELECT

For a SELECT, plain EXPLAIN asks the optimizer to describe the plan without executing the SELECT:

EXPLAIN SELECT id, created_at, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Use EXPLAIN FORMAT=JSON for nested details such as access paths and attached conditions. Cost values are optimizer estimates, not milliseconds.

Walk through an EXPLAIN result

Here is an illustrative traditional-format result for the query above, assuming an index on (customer_id, created_at) and an unpartitioned table:

+----+-------------+--------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys               | key                         | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_orders_customer_created | idx_orders_customer_created | 4       | const | 120  | 100.00   |       |
+----+-------------+--------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------+
  • type = ref: MySQL estimates a non-unique index lookup for the equality condition on customer_id.
  • key: The optimizer chose the composite index. possible_keys lists candidates; it does not mean every candidate will be used.
  • rows = 120 and filtered = 100.00: These are estimates used to reason about examined and passing rows, not measured counts or execution time. Statistics and parameter values can make them inaccurate.
  • Extra is empty: This illustrative plan does not report an extra operation. Do not infer runtime improvement from this field alone.

The row count, key length, filtered estimate, and selected access path are illustrative and depend on the actual table, indexes, statistics, and MySQL release. Compare with your own complete output. A plan with type = ALL or Using filesort may also be reasonable depending on table size, predicates, ordering, and workload. See how to interpret Using filesort and how composite index order affects a plan.

Read the important columns

  • table / id: Identify the query block and table. Join row order gives a useful view of the access sequence; subqueries and derived tables add complexity.
  • type: The access method. const, eq_ref, ref, and range often indicate selective access; ALL means a table scan. A scan can be correct for a small table or a query reading much of it.
  • possible_keys / key: Indexes considered and selected. key = NULL is not automatically a defect.
  • key_len: Index bytes used; interpretation depends on column types, nullability, and encoding.
  • rows / filtered: Estimated examined rows and estimated percentage passing the condition. Actual values can differ when statistics or data distribution are unrepresentative.
  • Extra: Details such as Using where, Using index, or Using filesort. Filesort does not necessarily mean disk I/O; Using index indicates covering index access.

Investigate a scan or estimate mismatch

Confirm the predicate, parameter values, table size, and indexes. Check for functions or implicit conversions on indexed columns, whether predicates match the leading columns of a composite index, and whether the query returns a large share of the table. If estimates look stale, review statistics and consider ANALYZE TABLE under your normal operational process; it updates key distribution statistics and may affect optimizer choices.

A scan is a symptom to explain, not an instruction to add an index. Indexes consume storage and add work to inserts, updates, and deletes.

Verify the hypothesis

  1. Record exact SQL, bind values, server version, schema, indexes, and relevant row counts.
  2. Compare plans for representative parameter values, including common and selective cases.
  3. On a safe non-production environment with representative data, use EXPLAIN ANALYZE for supported statements. It executes the statement and reports actual iterator timing and row counts.
  4. After a change, compare correctness, actual rows, latency distributions, and write overhead with a controlled baseline.

Do not run EXPLAIN ANALYZE on production without understanding its effects: it executes the statement. For modifying statements, use a safe test environment and version-specific documentation.

Primary sources

Editorial review

Written and reviewed by the TuningLens editorial team. Published 24 September 2026; updated 27 September 2026.

TuningLens analyzes evidence you submit; it does not connect to or execute queries on your database. Recommendations require review and testing in your environment.

Request beta access