MySQL EXPLAIN Explained: A Complete Guide to Reading Query Execution Plans

EXPLAIN is the single most useful tool for understanding MySQL query performance. When a query runs slower than expected, the first thing you should do is prefix it with EXPLAIN to inspect the execution plan. It reveals how MySQL processes your query – which indexes it picks, how many rows it expects to scan, and what join strategy it uses.

This guide walks through every field in the EXPLAIN output so you can quickly pinpoint performance bottlenecks.

Basic Usage

Using EXPLAIN is straightforward – just add the keyword before any SELECT statement:

EXPLAIN SELECT * FROM users WHERE id = 1;

MySQL 8.0 and later support several output formats:

-- Traditional tabular format (default)
EXPLAIN SELECT * FROM users;

-- JSON format with additional detail
EXPLAIN FORMAT=JSON SELECT * FROM users;

-- Tree format showing execution order
EXPLAIN FORMAT=TREE SELECT * FROM users;

-- Actually runs the query and reports runtime stats (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM users;

Warning: EXPLAIN ANALYZE executes the query for real, so be careful with statements that modify data.

The 12 EXPLAIN Output Fields

EXPLAIN returns a row per table involved in the query. Each row contains 12 columns:

Let’s examine each field in detail.

id – Query Sequence Number

The id indicates execution order:

• Same id: rows execute top to bottom
• Different ids: higher id executes first
• NULL id: represents a result set (e.g., UNION RESULT) rather than a table access

-- Subquery example
EXPLAIN
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

select_type – Query Type

The select_type tells you what kind of SELECT each row belongs to:

-- SIMPLE
EXPLAIN SELECT * FROM users WHERE id = 1;

-- SUBQUERY
EXPLAIN SELECT * FROM orders
WHERE user_id = (SELECT id FROM users WHERE name = 'test');

-- DERIVED
EXPLAIN SELECT * FROM (SELECT * FROM users WHERE status = 1) AS t;

table – Table Name

Shows which table the row describes. Special values include:

• <derivedN>: derived table from subquery with id N
• <unionM,N>: UNION result of ids M and N
• <subqueryN>: materialized subquery with id N

partitions

If the table is partitioned, this shows which partitions were accessed. NULL for non-partitioned tables.

type – Access Method (Critical)

The type column is one of the most important things to check. It describes how MySQL finds rows in the table. Ranked from best to worst performance:

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

Access Type Reference

Practical Examples

-- const: primary key equality
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const

-- ref: non-unique index lookup
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- type: ref (assuming a non-unique index on email)

-- range: range scan
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';
-- type: range (assuming an index on created_at)

-- ALL: full table scan
EXPLAIN SELECT * FROM users WHERE name LIKE '%test%';
-- type: ALL (leading wildcard prevents index use)

Rule of thumb: aim for range or better. If you see ALL on a large table, you almost certainly need an index.

possible_keys – Candidate Indexes

Lists every index that MySQL considered for this table. An index appearing here does not guarantee it will be used – it’s just a candidate.

key – Chosen Index

The index MySQL actually picked. NULL means no index was used.

Sometimes key shows an index not listed in possible_keys. This happens when MySQL discovers a covering index that satisfies the query entirely from the index without touching the table data.

key_len – Index Bytes Used

The number of bytes from the index that MySQL actually uses. This is especially useful for composite indexes – it tells you how many columns of the index are being utilized:

-- Suppose there is a composite index idx_name_age(name, age)
-- name VARCHAR(50), age INT

EXPLAIN SELECT * FROM users WHERE name = 'test';
-- key_len: 152 (50*3 + 2 for VARCHAR with utf8)

EXPLAIN SELECT * FROM users WHERE name = 'test' AND age = 25;
-- key_len: 157 (152 + 4 for INT + 1 for NULL flag)

key_len calculation rules:

• CHAR(n): n x character set bytes
• VARCHAR(n): n x character set bytes + 2
• INT: 4 bytes
• BIGINT: 8 bytes
• DATE: 3 bytes
• DATETIME: 8 bytes
• Nullable columns add 1 extra byte

ref – What’s Compared to the Index

Shows which columns or constants are matched against the index named in key. Common values:

• const: a literal value
• schema.table.column: a column from another table in a JOIN
• func: a function result

rows – Estimated Row Count

MySQL’s estimate of how many rows it needs to examine. This is an approximation based on index statistics, not an exact count.

Lower is better. A high rows value signals that the query may be doing more work than necessary.

filtered – Post-filter Percentage

The estimated percentage of rows that survive the WHERE clause after the initial access method.

rows x filtered% gives the estimated number of rows passed to the next step.

-- If rows = 1000 and filtered = 10.00
-- roughly 100 rows are expected to reach the next stage

Extra – Additional Execution Details (Critical)

The Extra column packs a lot of actionable information.

Performance Warning Signs

Good Signs

Other Common Values

Examples

-- Using index (covering index)
EXPLAIN SELECT id, name FROM users WHERE name = 'test';
-- With index idx_name(name), only id and name are needed

-- Using filesort
EXPLAIN SELECT * FROM orders ORDER BY amount;
-- No index on amount

-- Using temporary; Using filesort
EXPLAIN SELECT department, COUNT(*) FROM employees
GROUP BY department ORDER BY COUNT(*) DESC;

Visual Explain in MySQL Workbench

MySQL Workbench provides a Visual Explain feature that renders execution plans as interactive diagrams.

Color Coding

Reading Order

Visual Explain diagrams read bottom to top, left to right.

Real-World Optimization Examples

Example 1: Eliminating a Full Table Scan

-- Problem: full table scan
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type: ALL, rows: 100000

-- Fix: add an index
ALTER TABLE orders ADD INDEX idx_status(status);

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type: ref, rows: 500

Example 2: Removing filesort

-- Problem: extra sort operation
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at;
-- Extra: Using filesort

-- Fix: composite index matching both WHERE and ORDER BY
ALTER TABLE orders ADD INDEX idx_user_created(user_id, created_at);

EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at;
-- Extra: Using index condition (filesort gone)

Example 3: Using a Covering Index

-- Problem: table lookup required
EXPLAIN SELECT id, name, email FROM users WHERE name = 'test';
-- Extra: NULL

-- Fix: covering index that includes all selected columns
ALTER TABLE users ADD INDEX idx_name_email(name, email);

EXPLAIN SELECT id, name, email FROM users WHERE name = 'test';
-- Extra: Using index (no table access needed)

Example 4: Optimizing a Multi-Table JOIN

EXPLAIN
SELECT o.id, c.name, c.email
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2024-01-01';

Key things to check:

1. type for each table – aim for eq_ref or ref on the joined table
2. Indexes on join columns – c.id and o.customer_id should both be indexed
3. rows product – multiply the rows values across tables to estimate total work

Pro Tips

1. Use SHOW WARNINGS to See the Rewritten Query

EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);
SHOW WARNINGS;

The output reveals how the optimizer rewrote your query internally, which can explain unexpected execution plans.

2. Use FORMAT=JSON for Cost Details

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1\G

The JSON output includes cost estimates (cost_info), actual index parts used, and other details not shown in the tabular format.

3. Use EXPLAIN ANALYZE for Runtime Statistics

EXPLAIN ANALYZE SELECT * FROM users WHERE status = 1;

Unlike plain EXPLAIN, this actually runs the query and reports real execution times, actual row counts, and loop iterations for each step.

Summary

EXPLAIN is an essential tool for MySQL performance work. Focus on these four columns first:

General optimization strategies:

1. Add targeted indexes for columns in WHERE, JOIN, and ORDER BY clauses
2. Use covering indexes to eliminate table lookups
3. Avoid index-killing patterns like wrapping indexed columns in functions or using leading wildcards in LIKE
4. Optimize JOINs by driving from the smaller table and ensuring all join columns are indexed

References

• MySQL Official Docs: EXPLAIN Output Format
• MySQL Workbench Visual Explain
• PlanetScale: How to Read MySQL EXPLAINs
• MySQL Visual Explain Online Tool