Master MySQL Execution Plans: Decode EXPLAIN Output for Faster Queries

Introduction

Analyzing the EXPLAIN output is essential for writing high‑performance MySQL queries. The execution plan shows how the optimizer intends to retrieve rows and where potential bottlenecks exist.

Execution Plan Overview

An execution plan is generated by the optimizer from the SQL statement and table statistics. It lists each step MySQL will take, including which tables are accessed, which indexes are used, and estimated row counts.

id

Sequential identifier for each SELECT block in the plan.

A larger id has higher priority and is executed first when ids differ.

If ids are equal, rows are processed in the order they appear. NULL indicates a result set that does not require further table access.

select_type

simple – a plain SELECT without UNION or subqueries.

primary – the outermost SELECT of a UNION or a query containing subqueries.

union – a SELECT that appears after a UNION keyword.

derived – a subquery used as a temporary table in the FROM clause.

subquery – a subquery appearing outside the FROM clause.

dependent union – a UNION whose execution depends on an outer query.

dependent subquery – a subquery whose execution depends on an outer query.

explain select * from t3 where id=3952602;

explain select * from (select * from t3 where id=3952602) a;

explain select * from t3 where id = (select id from t3 where id=3952602);

table

Shows the table (or alias) accessed by the row.

If a temporary result is used, the name appears in angle brackets, e.g., <derived3>. NULL means the step does not touch a table.

type

Describes how MySQL searches rows in the table. The hierarchy from most efficient to least efficient is:

system

const

eq_ref

ref

fulltext

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

ALL

NULL

All types except ALL can use an index; only index_merge may combine multiple indexes. Aim for at least range, preferably ref or better.

possible_keys

Indexes that MySQL could consider for the query.

key

The actual index chosen by the optimizer.

If select_type is index_merge, multiple keys may be listed.

key_len

Number of bytes of the index used for the query. It reflects the maximum possible length, not the exact bytes read.

Only columns referenced in the WHERE clause contribute to key_len; ORDER BY or GROUP BY do not.

ref

Shows the column or constant compared with the key.

Values can be const, a column name, or func when an expression or function is involved.

rows

Estimated number of rows MySQL expects to examine for this step, based on table statistics.

extra

Using filesort – MySQL sorts the result set without using an index (often triggered by ORDER BY or GROUP BY that cannot be satisfied by an index).

Using temporary – A temporary table is created, typically for GROUP BY or complex ORDER BY. Disk‑based temporary tables should be avoided.

Not exists – Optimization of a LEFT JOIN where the search stops after the first matching row.

Using index – The query is covered by the index; data rows are not read from the table.

Using index condition – Index condition pushdown (available from MySQL 5.6) evaluates some predicates directly on the index.

Using where – The WHERE clause is applied after rows are fetched from the storage engine.

Using join buffer – A join buffer (Block Nested Loop or Batched Key Access) is used for the join.

impossible where – The WHERE condition can never be true, so no rows are returned.

select tables optimized away – MySQL can compute MIN(), MAX(), or COUNT(*) using only the index.

distinct – Scanning stops after the first matching row for each distinct value.

filtered

Appears in EXPLAIN EXTENDED (default from MySQL 5.7). Shows the percentage of rows filtered by the storage engine before the server applies additional conditions.

Limitations of MySQL EXPLAIN

Does not reveal the impact of triggers, stored procedures, or user‑defined functions.

Ignores cache effects such as the query cache or InnoDB buffer pool.

Cannot display runtime optimizations performed during execution.

Statistics are estimates, not exact counts.

Works only for SELECT statements; other statements must be rewritten as SELECT to be examined.

Case Study: Query Plan Analysis

Execution Order

(id = 4) SELECT id, name FROM t2 – select_type is union, the second SELECT in a UNION.

(id = 3) SELECT id, name FROM t1 WHERE address='11' – marked as derived; the WHERE uses a composite index idx_name_email_address, so type is index.

(id = 2) SELECT id FROM t3 – appears as a subquery inside the outer SELECT.

(id = 1) SELECT d1.name, … FROM … d1 – select_type is PRIMARY; the table column shows derived3, meaning it reads from the derived result of id = 3.

(id = NULL) UNION result – reads rows from the temporary UNION table; the table column shows union 1,4.