EXPLAIN Query Plan in MySQL

What MySQL EXPLAIN Tells You

EXPLAIN shows the execution plan chosen by the MySQL optimizer: table access order, access methods, candidate and selected indexes, estimated rows, filtering and extra operations. It helps answer how MySQL intends to obtain a result, not whether the SQL result is logically correct.

EXPLAIN supports SELECT and several data-changing statement forms. This lesson uses safe SELECT examples. It also separates ordinary EXPLAIN, which reports an estimated plan, from EXPLAIN ANALYZE, which executes a SELECT and reports measurements.

Build a Verified Query-Plan Lab

DROP TABLE IF EXISTS orders_plan_lab;
CREATE TABLE orders_plan_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  notes VARCHAR(100)
) ENGINE = InnoDB;

INSERT INTO orders_plan_lab VALUES
(1,101,'PAID',     '2026-08-01',1200.00,'School books'),
(2,101,'PENDING',  '2026-08-05', 500.00,'Stationery'),
(3,102,'PAID',     '2026-08-03', 750.00,'Uniform'),
(4,103,'CANCELLED','2026-08-04', 300.00,'Art kit'),
(5,101,'PAID',     '2026-08-10',1500.00,'Lab equipment'),
(6,102,'PENDING',  '2026-08-11', 900.00,'Sports kit'),
(7,104,'PAID',     '2026-08-12',2200.00,'Computer accessory'),
(8,101,'PAID',     '2026-08-14', 650.00,'Notebooks');

SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

First verify the result. Then study the access path. This lab is intentionally tiny, so MySQL may prefer a scan before or even after an index is added. That is not an error; scale and distribution determine cost.

Traditional, TREE and JSON Formats

-- Tabular output
EXPLAIN
SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID';

-- Iterator-style hierarchy
EXPLAIN FORMAT=TREE
SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID';

-- Machine-readable details
EXPLAIN FORMAT=JSON
SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID';

MySQL 8.4 can have a server-level explain_format default, so specify a FORMAT when reproducible output matters. EXPLAIN ANALYZE always uses TREE; TRADITIONAL and JSON are not supported for ANALYZE.

Read Traditional EXPLAIN Columns

For InnoDB, rows is an estimate. In a join, rough fan-out can be explored by multiplying estimated rows and filtering effects across steps, but TREE/JSON and measured actuals provide a more complete picture.

Understand Access Types Without Memorized Hype

The often-repeated ranking from const to ALL is only a starting vocabulary. A ref access returning millions of rows can cost more than a small scan. Join order, row counts, loops, page access, coverage and result size matter.

CREATE INDEX idx_customer_status_date
ON orders_plan_lab (customer_id, status, order_date);

EXPLAIN FORMAT=JSON
SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

Inspect key and used_key_parts. On eight rows, do not force the index merely to make the output look impressive.

Decode Important Extra Signals

Using filesort or Using temporary deserves attention when row counts and latency are large, but neither is an automatic failure. Sorting ten rows can be cheaper than maintaining a wide index for every write.

-- Check whether index order can serve filtering and sorting together.
EXPLAIN
SELECT order_id, order_date
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

EXPLAIN ANALYZE: Estimates vs Actuals

EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id, order_date, total_amount
FROM orders_plan_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

For each iterator, ANALYZE reports estimated cost/rows and measured time to first row, time range, actual rows and loops. Compare:

• estimated rows vs actual rows: large errors can distort join and access choices;
• loops: a cheap inner operation repeated thousands of times can dominate;
• time to first row vs total: useful for interactive latency and blocking sorts;
• rows flowing between nodes: find where filtering happens too late;
• selected index and lookup count: confirm intended access.

If estimates are badly wrong after major data changes, inspect data skew and refresh key distribution with ANALYZE TABLE orders_plan_lab;. Histograms can help estimates for appropriate nonindexed columns, but they are not a substitute for access indexes.

Evidence-Based Query Tuning Workflow

1. Capture the exact SQL, parameters, frequency, latency and rows sent/examined.
2. Verify logical output and obtain a reproducible representative dataset.
3. Run EXPLAIN in TREE or JSON and identify the largest estimated work.
4. Use EXPLAIN ANALYZE safely to compare actual rows, loops and timing.
5. Check predicate sargability, data types, join keys and selected columns.
6. Design the smallest workload-aligned composite index or SQL rewrite.
7. Run the same measurements before and after; include write/storage impact.
8. Test edge parameter values, because selectivity can change the best plan.
9. Deploy safely, monitor regressions and keep a rollback path.

Common mistakes: adding an index for every ALL, treating rows as exact, confusing Using index with “index chosen,” ignoring loops, tuning an unrepresentative empty database, and using FORCE INDEX before fixing statistics or design.

Continue with composite indexes and the full query optimization guide.

Official References

• MySQL 8.4: EXPLAIN Statement and EXPLAIN ANALYZE
• MySQL 8.4: EXPLAIN Output Format
• MySQL 8.4: Optimizing Queries with EXPLAIN

Formats, fields, access methods, Extra signals and ANALYZE execution semantics were checked against the official MySQL 8.4 manual.