EXPLAIN usage and examples

Overview

EXPLAIN shows how the optimizer plans table access, indexes, joins, and sorting, but ordinary EXPLAIN does not prove elapsed time. Start with access type, chosen keys, estimated rows, and extra work, then retest with production-like data distribution. A different plan is not automatically a faster plan.

Basic syntax

EXPLAIN SELECT * FROM orders WHERE department = 'Sales';

Examples

EXPLAIN example

The example inspects access for a department predicate. The page shows a learning-oriented summary; real decisions require native EXPLAIN from the target MySQL instance.

Sample table

Table：orders

orders
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Run result

query_plan_summary
step	object	access	summary	note
1	orders	sequential scan	Read orders sequentially and apply the query predicate.	This table is a conceptual access summary, not the native MySQL EXPLAIN columns. Use the actual plan from the target MySQL environment for index choice, cost, and row estimates.

Common issues

Does EXPLAIN actually run the query?

Plain EXPLAIN normally produces a plan. EXPLAIN ANALYZE executes and measures it, so do not use it casually on writes or expensive statements.

What should I inspect first in an execution plan?

Start with access type, estimated rows, chosen indexes, join order, and signs of temporary tables or extra sorting.

Does a better-looking plan prove the query is faster?

No. Estimates are clues; compare elapsed time, rows read, and result equivalence on production-like data.

Keep reading

No related topics yet

EXPLAIN