Reading a query plan with EXPLAIN

Problem and reasoning

EXPLAIN answers how the optimizer plans to run a statement, not how many milliseconds it ultimately takes. In MySQL 8 tabular output, start with type, possible_keys, key, rows, filtered, and Extra to assess each table's access path and estimated work.

Access types from ALL or index toward range, ref, and const generally narrow the lookup, but data size still matters. A full scan can be sensible for a small table. possible_keys lists candidates; key identifies the index actually selected.

Calculating on or wrapping an indexed column can prevent a regular B-tree lookup. id + 0 = 3 and id = 3 return the same row, but the direct equality is easier to recognize as a primary-key lookup. Such index-searchable conditions are often called sargable predicates.

Use EXPLAIN ANALYZE when actual execution measurements are needed, but remember that it really runs the statement. Check safety before updates, deletes, or expensive queries. Results also depend on distribution, statistics, cache state, and parameter values.

After a change, verify both correctness and plan evidence: examined rows, selected index, remaining filesort or temporary work, and the write and storage cost introduced by any new index.

Examples

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

Before / Not recommended

EXPLAIN SELECT * FROM orders WHERE id + 0 = 3;

Before 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.

After / Recommended

EXPLAIN SELECT * FROM orders WHERE id = 3;

After result

query_plan_summary
step	object	access	summary	note
1	orders	index/key lookup	Read orders through an index or key path in the example data.	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.

Why it is better：Both predicates identify the same row, but direct primary-key equality forms a clear key lookup; calculating on the column prevents a normal direct index lookup.