TuningLens

MySQL guide · Published and updated 24 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.

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 and updated 24 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