Skip to content

CONCEPT Cited by 2 sources

MySQL EXPLAIN

EXPLAIN is the MySQL keyword that asks the query optimizer to describe the execution plan it would choose for a query — without running the query. Prefix any SELECT, UPDATE, DELETE, or TABLE statement with EXPLAIN and MySQL emits one row per table accessed in the plan, with columns describing how each table will be joined, which indexes MySQL could use, which one it actually chose, an estimate of how many rows will be scanned, and what extra work happens (temporary tables, filesorts, index-only scans, etc.).

When you prepend the EXPLAIN keyword to the beginning of a query, it explains how the database executes that query and the estimated costs.

— sources/2026-04-21-planetscale-how-to-read-mysql-explains

The output columns

PlanetScale's Savannah Longoria enumerates what EXPLAIN emits per plan row:

Column Meaning
id Integer identifying which SELECT this row belongs to (for subqueries / UNIONs)
select_type SIMPLE (no subqueries/UNIONs), PRIMARY (complex — subquery, derived table, UNION), DELETE, UPDATE, etc.
table Which table this row's access describes
partitions Which partitions are accessed (if the table is partitioned)
type Access type — how MySQL finds rows in the table (best→worst: NULL, system, const, eq_ref, ref, range, index, ALL)
possible_keys Which indexes MySQL could have used
key Which index MySQL actually chose
key_len Length in bytes of the chosen index — the only way to tell how many columns of a composite index are in use
ref Which columns / constants are compared against the index
rows Estimated number of rows MySQL must examine to satisfy the query
filtered Pessimistic estimate of the percentage of those rows that will survive the WHERE / join condition
Extra Additional info — Using index, Using where, Using temporary, Using filesort, Backward index scan, etc.

key_len as the composite-index usage signal

For a composite index on (last_name, first_name) where last_name VARCHAR(16) (16 × utf8mb4-worst-case 4 bytes + 2-byte length prefix = 66) and first_name VARCHAR(14) (14 × 4 + 2 = 58), key_len of ~66 means only the last_name prefix is in use; ~124 means both columns are. The column is MySQL's only way to surface partial composite-index utilisation — if you added a two-column index expecting both to be used and key_len is half what you expect, the plan is only using the leading column.

rows × filtered ≈ rows actually joined

The post makes the multiplication explicit:

If you multiply the rows column by this percentage, you will see the number of rows MySQL estimates it will join with the previous tables in the query plan.

rows = 299,202 with filtered = 100% means the optimiser thinks it'll feed 299,202 rows into the next join stage — an O(N) scan of the whole table. rows = 1 with filtered = 100% is an index lookup hitting a single row (the canonical post-optimisation shape — see patterns/composite-index-for-and-predicate).

The canonical "before/after" pattern

-- Before: no index covering (last_name, first_name)
EXPLAIN SELECT * FROM employees
WHERE last_name = 'Puppo' AND first_name = 'Kendra';
-- type: ALL, key: NULL, rows: 299,202

CREATE INDEX fullnames ON employees(last_name, first_name);

-- After
EXPLAIN SELECT * FROM employees
WHERE last_name = 'Puppo' AND first_name = 'Kendra';
-- type: ref, key: fullnames, rows: 1

The rows column collapsing from 299,202 → 1 is the reader's feedback loop that the composite index is being used. (Source: sources/2026-04-21-planetscale-how-to-read-mysql-explains.)

SHOW WARNINGS — when EXPLAIN refuses to parse

If the query doesn't parse cleanly, EXPLAIN returns an error rather than a plan. Running SHOW WARNINGS; immediately after surfaces MySQL's rewriter hints — partial structural observations about the query fragments it managed to process:

Marker Meaning
<index_lookup>(fragment) Index lookup would have happened here
<primary_index_lookup>(fragment) Primary-key lookup would have happened
<if>(cond, expr1, expr2) Conditional branch
<temporary table> Internal temporary table would be created (e.g. pre-join subquery materialisation)

Useful when a malformed query gives you nothing else to debug against.

Known limitations

The article is explicit that EXPLAIN is an approximation — sometimes a good one, sometimes far from the truth:

  • Says nothing about triggers, stored functions, or UDFs.
  • Doesn't work for stored procedures.
  • Doesn't reflect runtime optimisations MySQL performs during execution.
  • rows and filtered are estimates from statistics — can be wildly wrong if stats are stale or the data is skewed.
  • Collapses distinctions: "filesort" covers both in-memory and on-disk sorts; "Using temporary" covers both in-memory and on-disk temp tables. EXPLAIN won't tell you which.

For actual runtime numbers, see EXPLAIN ANALYZE (MySQL 8.0.18+) which runs the query and reports real per-iterator timing.

Output formats

MySQL CLI can emit EXPLAIN output as:

  • Tabular (default) — one row per plan row, columns as above.
  • Tabbed — tab-separated.
  • Vertical (\G) — key/value pairs, easier for wide-column plans.
  • Traditional tree (FORMAT=TREE, 8.0.16+) — ASCII tree of iterators, parent-child flow visible.
  • JSON — pretty or raw — full optimiser detail. Raw JSON is not supported for EXPLAIN ANALYZE.

Relationship to the broader wiki

EXPLAIN is the operational inspection surface that makes the secondary-index, composite-index, and clustered-index pages verifiable in practice. Every index-design exercise in the wiki — the generated-hash-column pattern, the composite-index for AND-predicate pattern, B+tree primary-key choice — is ultimately verified by running EXPLAIN before/after and watching type: ALL, rows: N flip to type: ref, rows: 1. See patterns/explain-before-execute-validation for the LLM-generated-SQL variant that uses EXPLAIN as a compilation-validity gate rather than a performance-tuning tool.

Seen in

Last updated · 378 distilled / 1,213 read