SQL Tutorial › Performance & Objects › EXPLAIN & Query Tuning

SQL EXPLAIN and Query Optimization

Q: How do I find out why a MySQL query is slow?

Run EXPLAIN (or EXPLAIN ANALYZE) on it. Look for type = ALL on large tables, key = NULL, very high rows, and Using filesort or Using temporary in Extra.

AdvancedLesson 61 of 6913 min read7 queries run on MySQL 8.4

When a query is slow, guessing does not help: ask the database how it runs the query. EXPLAIN shows the plan MySQL chose: which tables it reads in which order, which indexes it uses and how many rows it expects. This lesson reads real plans column by column and turns them into a checklist for faster SQL.

In this lesson

Read the key columns of EXPLAIN: type, key, rows, Extra
See the actual run with EXPLAIN ANALYZE
Fix common slow patterns
Follow a practical optimization checklist

A first EXPLAIN

MySQL

EXPLAINSELECT o.order_id, c.customer_nameFROM orders oJOIN customers c ON c.customer_id = o.customer_idWHERE o.status = 'Pending';

ResultMySQL 8.4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	NULL	ALL	customer_id	NULL	NULL	NULL	1	100	Using where
1	SIMPLE	c	NULL	eq_ref	PRIMARY	PRIMARY	4	company.o.customer_id	1	100	NULL

2 rows in set

How this query works, step by step

Each row of the output is one table access, in the order MySQL performs them.
orders is read first with type = ALL (there is no index on status), and filtered estimates the share of rows that pass the WHERE.
For each such order, customers is read with type = eq_ref through its PRIMARY key: exactly one row per lookup.

The columns that matter

Column	Tells you
`table`	which table this step reads
`type`	how it is read (see below)
`possible_keys`	indexes MySQL considered
`key`	the index actually used (`NULL` = none)
`rows`	estimated rows read in this step
`filtered`	estimated % of those rows that pass the conditions
`Extra`	notes such as `Using where`, `Using index`, `Using filesort`, `Using temporary`

type, from best to worst:

type	Meaning
`const` / `system`	at most one row, found by primary key or unique key with a constant
`eq_ref`	one row per row of the previous table (join on a unique key)
`ref`	several rows via a non-unique index
`range`	an index range (`BETWEEN`, `>`, `IN`, `LIKE 'x%'`)
`index`	full scan of an index
`ALL`	full table scan

const and ref

MySQL

EXPLAIN SELECT * FROM employees WHERE emp_id = 105;

ResultMySQL 8.4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	const	PRIMARY	PRIMARY	4	const	1	100	NULL

1 row in set

MySQL

EXPLAIN SELECT * FROM employees WHERE dept_id = 2;

ResultMySQL 8.4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	ref	dept_id	dept_id	5	const	3	100	NULL

1 row in set

How this query works, step by step

Looking up the primary key with a constant is const: MySQL knows at most one row matches.
dept_id has a (foreign key) index, so the second query is ref with key = dept_id.

EXPLAIN ANALYZE: the real run

EXPLAIN ANALYZE executes the query and reports actual times and row counts next to the estimates, as a tree:

MySQL

EXPLAIN ANALYZESELECT c.customer_name, COUNT(*) AS ordersFROM customers cJOIN orders o ON o.customer_id = c.customer_idGROUP BY c.customer_name;

ResultMySQL 8.4

EXPLAIN
-> Table scan on <temporary> (actual time=0.112..0.112 rows=5 loops=1) -> Aggregate using temporary table (actual time=0.111..0.111 rows=5 loops=1) -> Nested loop inner join (cost=0.7 rows=1) (actual time=0.0232..0.0317 rows=10 loops=1) -> Covering index scan on o using customer_id (cost=0.35 rows=1) (actual time=0.0061..0.0101 rows=10 loops=1) -> Single-row index lookup on c using PRIMARY (customer_id=o.customer_id) (cost=0.35 rows=1) (actual time=760e-6..800e-6 rows=1 loops=10)

EXPLAIN

-> Table scan on <temporary> (actual time=0.112..0.112 rows=5 loops=1) -> Aggregate using temporary table (actual time=0.111..0.111 rows=5 loops=1) -> Nested loop inner join (cost=0.7 rows=1) (actual time=0.0232..0.0317 rows=10 loops=1) -> Covering index scan on o using customer_id (cost=0.35 rows=1) (actual time=0.0061..0.0101 rows=10 loops=1) -> Single-row index lookup on c using PRIMARY (customer_id=o.customer_id) (cost=0.35 rows=1) (actual time=760e-6..800e-6 rows=1 loops=10)

1 row in set

Read it from the most-indented line outwards: the innermost lines run first. Compare rows= estimates with actual ... rows=: a big difference means stale statistics (ANALYZE TABLE t; refreshes them). The timings are from the build server and change on every run.

EXPLAIN FORMAT=TREE shows the same tree without running the query.

Fix: an index for a filter

MySQL

CREATE INDEX idx_orders_status ON orders (status);EXPLAIN SELECT order_id FROM orders WHERE status = 'Pending';

ResultMySQL 8.4

mysql> CREATE INDEX idx_orders_status ON orders (status)

Query OK, 0 rows affected

mysql> EXPLAIN SELECT order_id FROM orders WHERE status = 'Pending'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	NULL	ref	idx_orders_status	idx_orders_status	82	const	1	100	Using index

1 row in set

Before the index the plan was ALL; now it is ref on idx_orders_status.

Fix: avoid filesort with an index on the ORDER BY

MySQL

EXPLAIN SELECT order_id, order_date FROM orders ORDER BY order_date DESC LIMIT 3;

ResultMySQL 8.4

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	NULL	ALL	NULL	NULL	NULL	NULL	10	100	Using filesort

1 row in set

MySQL

CREATE INDEX idx_orders_date ON orders (order_date);EXPLAIN SELECT order_id, order_date FROM orders ORDER BY order_date DESC LIMIT 3;

ResultMySQL 8.4

mysql> CREATE INDEX idx_orders_date ON orders (order_date)

Query OK, 0 rows affected

mysql> EXPLAIN SELECT order_id, order_date FROM orders ORDER BY order_date DE…

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	NULL	index	NULL	idx_orders_date	3	NULL	3	100	Backward index scan; Using index

1 row in set

How this query works, step by step

Without an index on order_date, Extra shows Using filesort: MySQL sorts all rows to find the top 3.
With the index, MySQL reads the index backwards and stops after 3 entries: no filesort.

Optimization checklist

Select only the columns you need; avoid SELECT * on wide tables.
Index the columns in WHERE, JOIN and ORDER BY of frequent queries; check with EXPLAIN that key is not NULL.
Keep columns bare in WHERE: order_date >= '2025-01-01', not YEAR(order_date) = 2025.
Avoid leading wildcards (LIKE '%term') on large tables; use full-text search.
Match types: compare text columns with text, numbers with numbers.
Filter early: put conditions in WHERE rather than HAVING when they do not need aggregates.
Use EXISTS / joins instead of IN on huge subquery lists; prefer NOT EXISTS to NOT IN.
Paginate with keyset (WHERE id > last_id) instead of huge OFFSETs.
Use UNION ALL instead of UNION when duplicates are impossible.
Measure: use EXPLAIN ANALYZE and the slow query log, change one thing at a time, and measure again.

Quick quiz

1. Which type is the worst?

2. key = NULL in EXPLAIN means…

3. What does Using filesort indicate?

4. What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN and optimization FAQ

How do I find out why a MySQL query is slow?

Run EXPLAIN (or EXPLAIN ANALYZE) on it. Look for type = ALL on large tables, key = NULL, very high rows, and Using filesort or Using temporary in Extra.

What does EXPLAIN do in SQL?

It shows the execution plan the optimizer chose: the order of table access, the indexes used and the estimated number of rows.

How can I optimize a SQL query?

Add indexes that match the filters, joins and sorts; keep indexed columns free of functions; return only needed columns and rows; and verify every change with EXPLAIN ANALYZE.