EXPLAIN and Execution Plans

EXPLAIN shows the execution plan the optimizer chose for a statement: which tables it touches, in what order, which index (if any) it uses per table, and roughly how many rows it expects to examine. It does not run the query (except when combined with ANALYZE, below); it only reports the plan. EXPLAIN works on SELECT, and also on INSERT, UPDATE, DELETE, and REPLACE since MySQL 8.0.19.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Basic form – one output row per table the optimizer plans to access.

The Classic Output Columns

A traditional (non-JSON) EXPLAIN returns one row per table with these columns:

id	Sequence number of the SELECT within the query. Subqueries and unioned SELECTs get their own id.
select_type	SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, etc.
table	The table (or derived table alias) this row describes.
partitions	Matching partitions, if the table is partitioned (see Partitioning).
type	The join/access method – the single most important column. See below.
possible_keys	Indexes the optimizer considered.
key	The index actually chosen, or NULL if a full scan was used.
key_len	Bytes of the chosen index actually used – useful for confirming how much of a composite index is engaged.
ref	What is compared against the index: a constant, a column from another table, or func.
rows	Estimated rows the optimizer expects to examine for this table (an estimate, not an exact count).
filtered	Estimated percentage of those rows that survive the WHERE conditions.
Extra	Additional plan details – often the most actionable column. See below.

Reading the type Column

type ranks how rows for a table are located, from best to worst:

system / const	At most one matching row, read once – e.g. a lookup by PRIMARY KEY on a constant.
eq_ref	One matching row per outer row, via a unique or primary key – typical for well-indexed joins.
ref	Multiple matching rows via a non-unique index equality lookup.
range	An index range scan (<, >, BETWEEN, IN, LIKE 'prefix%').
index	A full scan of an index (not the table), still reads every entry but can avoid a sort or a table read.
ALL	Full table scan – no usable index. Acceptable for tiny tables, a red flag on large ones.

As a rule of thumb, anything at range or better scales with the number of matching rows; index and ALL scale with the size of the whole table (or index).

The Extra Column

Using index	A covering index satisfied the query without touching the table row – the ideal outcome.
Using where	A WHERE condition filters rows after the storage engine returns them (in addition to any index lookup).
Using index condition	Index Condition Pushdown – part of the WHERE clause is evaluated against the index before rows are fetched.
Using temporary	MySQL builds a temporary table, typically for GROUP BY/DISTINCT over columns that don't match the index used, or certain UNIONs. Expensive on large result sets.
Using filesort	An extra sort pass is required because ORDER BY couldn't be satisfied by index order. Despite the name, it may happen in memory; it's still added CPU/I/O work.
Using join buffer	No index was usable for the join, so MySQL buffers rows and compares them in batches (Block Nested-Loop or hash join). See JOIN Optimization.

"Using temporary" and "Using filesort" together on a large table are the two most common signals that a query needs a better index or a rewrite.

A Worked Example

Given orders(id PK, customer_id, status, created_at) with an index on (customer_id, status):

EXPLAIN SELECT id, status FROM orders WHERE customer_id = 42 AND status = 'SHIPPED';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	ref	idx_cust_status	idx_cust_status	153	const,const	4	100.00	Using index

Both predicates use the composite index (type=ref, key_len covers both columns), and because id is the primary key and status is in the index, the whole query is satisfied from the index (Using index) – no row lookup needed.

EXPLAIN ANALYZE

EXPLAIN ANALYZE (MySQL 8.0.18+) actually executes the statement and reports real timings and row counts per plan node instead of estimates. It has no traditional tabular form – output is a tree:

EXPLAIN ANALYZE SELECT id, status FROM orders WHERE customer_id = 42 AND status = 'SHIPPED';

-> Index lookup on orders using idx_cust_status (customer_id=42, status='SHIPPED') (cost=1.2 rows=4) (actual time=0.045..0.052 rows=3 loops=1)

Compare rows (estimate) against actual rows: a large gap between them means the optimizer's statistics are stale (run ANALYZE TABLE) or the predicate isn't sargable (see Query Rewriting). Because it runs the query for real, avoid EXPLAIN ANALYZE on write statements or on expensive queries against production data.

EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON exposes fields the tabular form omits: per-table cost estimates, whether a derived table was materialized, and details of temporary table usage. It's most useful when comparing two candidate plans' cost figures directly, or when scripting plan inspection.

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

For background on how the optimizer chooses a plan in the first place, see Query Optimizer. For designing the indexes these plans depend on, see Index Strategy and Indexes.

CREATE TABLE orders (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id  INT UNSIGNED NOT NULL,
  status       VARCHAR(20) NOT NULL,
  created_at   DATETIME NOT NULL,
  KEY idx_cust_status (customer_id, status)
) ENGINE=InnoDB;

-- Classic tabular plan
EXPLAIN SELECT id, status
FROM orders
WHERE customer_id = 42 AND status = 'SHIPPED';

-- Real timings and row counts (executes the query)
EXPLAIN ANALYZE
SELECT id, status
FROM orders
WHERE customer_id = 42 AND status = 'SHIPPED';

-- Machine-readable plan with cost detail
EXPLAIN FORMAT=JSON
SELECT id, status
FROM orders
WHERE customer_id = 42 AND status = 'SHIPPED';

-- Refresh optimizer statistics when EXPLAIN's row estimate
-- diverges sharply from EXPLAIN ANALYZE's actual rows
ANALYZE TABLE orders;

CREATE TABLE orders (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id  INT UNSIGNED NOT NULL,
  status       VARCHAR(20) NOT NULL,
  created_at   DATETIME NOT NULL,
  KEY idx_cust_status (customer_id, status)
) ENGINE=InnoDB;

-- Classic tabular plan
EXPLAIN SELECT id, status
FROM orders
WHERE customer_id = 42 AND status = 'SHIPPED';

-- Real timings and row counts (executes the query)
EXPLAIN ANALYZE
SELECT id, status
FROM orders
WHERE customer_id = 42 AND status = 'SHIPPED';

-- Machine-readable plan with cost detail
EXPLAIN FORMAT=JSON
SELECT id, status
FROM orders
WHERE customer_id = 42 AND status = 'SHIPPED';

-- Refresh optimizer statistics when EXPLAIN's row estimate
-- diverges sharply from EXPLAIN ANALYZE's actual rows
ANALYZE TABLE orders;

MENU