Understanding MySQL EXPLAIN Execution Plans from Scratch

Why EXPLAIN Is Needed

Prefixing a query with EXPLAIN shows MySQL’s execution plan: join order, access method for each table, and estimated row counts. The examples use orders and users tables each holding tens of thousands of rows.

id and table: Which Row Describes Which Table

The table column displays the table name. The id column is assigned per SELECT keyword. In a simple join both rows share the same id; subqueries and UNION introduce additional id values. When the optimizer rewrites a subquery into a join, the id values become identical, exposing the transformation.

EXPLAIN SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id;
-- orders, users rows have id = 1 (orders is the driving table)

EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users) OR status = 'closed';
-- orders id = 1, users (subquery) id = 2

For UNION a row with id = NULL and table = <union1,2> appears; UNION ALL does not.

select_type: Role of Each Sub‑query

SIMPLE : No UNION or subquery (plain join)

PRIMARY : Outer‑most query in a multi‑query statement

UNION : All UNION/UNION ALL queries except the first

UNION RESULT : Temporary table created for UNION deduplication

SUBQUERY : Independent subquery materialized once

DEPENDENT SUBQUERY : Subquery that depends on outer query values

DERIVED : Derived table from a subquery in the FROM clause

MATERIALIZED : Subquery materialized then joined to outer table

SUBQUERY vs DEPENDENT SUBQUERY differ by whether the subquery references outer columns. DERIVED vs MATERIALIZED differ by whether the materialized table is queried directly or joined back.

-- Independent subquery
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users) OR status = 'closed';

-- Dependent subquery
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE users.province = orders.province) OR status = 'closed';

-- Derived table
EXPLAIN SELECT * FROM (SELECT user_id, COUNT(*) c FROM orders GROUP BY user_id) t WHERE c > 3;

-- Materialized subquery
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users);

type: Access‑Method Performance Ranking

The type column itself is a ranking from best to worst:

system → const → eq_ref → ref → fulltext → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → ALL

Examples for each type:

system : Single‑row MyISAM table.

CREATE TABLE site_config (id INT) ENGINE=MyISAM;
INSERT INTO site_config VALUES (1);
EXPLAIN SELECT * FROM site_config; -- type: system

const : Primary key equals a constant.

EXPLAIN SELECT * FROM orders WHERE id = 1001; -- type: const

eq_ref : Driving table joins on a primary/unique key.

EXPLAIN SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id; -- users type: eq_ref

ref : Ordinary secondary index equality.

EXPLAIN SELECT * FROM orders WHERE user_id = 1001; -- type: ref

fulltext : Full‑text index match.

ALTER TABLE orders ADD FULLTEXT INDEX idx_remark_ft (remark);
EXPLAIN SELECT * FROM orders WHERE MATCH(remark) AGAINST('春节 发货'); -- type: fulltext

ref_or_null : ref plus possible NULL match.

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 OR user_id IS NULL; -- type: ref_or_null

index_merge : Multiple indexes on the same table.

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 OR status = 'closed'; -- type: index_merge

unique_subquery : IN turned into EXISTS with primary‑key lookup.

EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE users.province = orders.province) OR status = 'closed'; -- users type: unique_subquery

index_subquery : Same as above but using a secondary index.

EXPLAIN SELECT * FROM orders WHERE remark IN (SELECT province FROM users WHERE users.id = orders.user_id) OR status = 'closed'; -- users type: index_subquery

range : Index range scan.

EXPLAIN SELECT * FROM orders WHERE user_id > 1000 AND user_id < 2000; -- type: range

index : Full index scan (covering index).

EXPLAIN SELECT city FROM orders WHERE district = '海淀区'; -- type: index

ALL : Full table scan. EXPLAIN SELECT * FROM orders; -- type: ALL Rule of thumb: everything except ALL uses an index; only one index is used per table except index_merge.

possible_keys / key / key_len

possible_keys

lists candidate indexes; key is the optimizer’s final choice. key_len shows how many bytes of a composite index are actually used, revealing how many columns participate.

EXPLAIN SELECT * FROM orders WHERE user_id > 100000 AND status = 'closed';
-- possible_keys: idx_user_id, idx_status
-- key: idx_status

Too many candidate indexes slow the optimizer; drop unused ones.

Example with composite index idx_area(province, city, district) (VARCHAR(50), utf8 = 3 bytes each):

-- Using only the first column
EXPLAIN SELECT * FROM orders WHERE province = '北京市';
-- key_len: 153

-- Using first two columns
EXPLAIN SELECT * FROM orders WHERE province = '北京市' AND city = '朝阳区';
-- key_len: 306

When key_len grows from 153 to 306, a second column of the composite index is being used.

ref / rows / filtered

ref

shows what the equality matches (constant, column, or function). rows is the optimizer’s estimated row count; filtered is the percentage that passes remaining predicates. Multiplying rows by filtered gives the driver‑table fan‑out, a key factor for choosing the driving table.

EXPLAIN SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id WHERE orders.status = 'closed';
-- orders (driver): rows = 20000, filtered = 5.00
-- fan‑out ≈ 20000 × 5% = 1000

A larger fan‑out means the driver table will be accessed more times, influencing optimizer decisions.

Extra: Common Hints

Using index : Covering index, no row lookup needed

Using index condition : Index condition pushdown (ICP)

Using where : Additional server‑side filtering

Using join buffer (Block Nested Loop) : Driver table cannot use index, falls back to in‑memory nested loop

Using filesort : Sorting cannot use index, requires external sort

Using temporary : Temporary table needed for DISTINCT or GROUP BY

Not exists : Optimization for LEFT JOIN … IS NULL

Using intersect(...) , union(...) , sort_union(...) : Three index‑merge strategies

Start temporary / End temporary : Semi‑join DuplicateWeedout strategy

LooseScan : Semi‑join LooseScan strategy

FirstMatch(tbl_name) : Semi‑join FirstMatch strategy

Index condition pushdown lets the storage engine evaluate both a range condition and a non‑range condition on the same index column before fetching rows, avoiding unnecessary table lookups.

EXPLAIN SELECT * FROM orders WHERE order_no > 'ORD20240000' AND order_no LIKE '%99';
-- Extra: Using index condition
-- Both conditions are pushed down, saving many row fetches

If a condition cannot be pushed down (e.g., column without index), Using where appears.

EXPLAIN SELECT * FROM orders WHERE remark = '春节延迟发货';
-- Extra: Using where

Using temporary

appears for queries with DISTINCT or GROUP BY. Because GROUP BY implicitly adds ORDER BY, Using filesort often shows; adding ORDER BY NULL suppresses it.

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using temporary; Using filesort

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY NULL;
-- Extra: Using temporary

JSON‑Formatted Execution Plan

Appending FORMAT=JSON to EXPLAIN returns a JSON object with a cost_info section. The prefix_cost of the last table equals the total estimated cost of the query, useful for comparing alternatives.

EXPLAIN FORMAT=JSON SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id WHERE orders.status = 'closed';

{
  "cost_info": {
    "read_cost": "980.32",
    "eval_cost": "102.15",
    "prefix_cost": "1082.47",
    "data_read_per_join": "2M"
  }
}

The final table’s prefix_cost (here 1082.47) represents the overall estimated cost.

SHOW WARNINGS: Seeing Optimizer Rewrites

Running SHOW WARNINGS after EXPLAIN shows how the optimizer transformed the statement. Example: a LEFT JOIN becomes an inner join when a WHERE clause filters out NULL rows.

EXPLAIN SELECT orders.order_no, users.mobile FROM orders LEFT JOIN users ON orders.user_id = users.id WHERE users.mobile IS NOT NULL;
SHOW WARNINGS;
-- Message: LEFT JOIN turned into JOIN because the IS NOT NULL condition removes the need for preserving unmatched rows.

The warning message is for understanding only; it is not executable SQL.

Putting It All Together

Read the columns in order: table/id/select_type, type/possible_keys/key/key_len, ref/rows/filtered, and Extra. This sequence lets you locate the step where a slow query stalls and decide how to rewrite or index the query for better performance.