Debug a bad plan with EXPLAIN ANALYZE and the optimizer trace

Command

-- Plan + actual time and rows (the query actually runs)
EXPLAIN ANALYZE
SELECT region, COUNT(*) FROM shop.big WHERE id < 20000 GROUP BY region;

-- Why the optimizer chose this plan: optimizer trace
SET SESSION optimizer_trace = 'enabled=on', SESSION optimizer_trace_max_mem_size = 1048576;
SELECT * FROM shop.big WHERE region = 'TR' AND id > 100 LIMIT 5;
SELECT JSON_PRETTY(JSON_EXTRACT(TRACE, '$**.considered_execution_plans')) AS plans,
       MISSING_BYTES_BEYOND_MAX_MEM_SIZE
FROM information_schema.OPTIMIZER_TRACE;
SET SESSION optimizer_trace = 'enabled=off';

What it does

EXPLAIN ANALYZE shows estimated (cost, rows) and actual (actual time, rows, loops) values side by side for each plan step; the optimizer trace shows the access paths considered, their costs and why each was chosen or rejected.

When to use it

When a query suddenly slows down (plan change), the optimizer picks the wrong index, or to find the source of an estimated vs. actual row mismatch.

Caution

EXPLAIN ANALYZE really executes the query; be careful with heavy queries on production. It works with SELECT, TABLE and multi-table UPDATE / DELETE. A gap of more than 10x between estimated and actual rows points to a statistics / histogram problem (ANALYZE TABLE, histograms). On the inner side of a nested loop, actual rows is per loop; total = rows × loops. If MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0 the trace was truncated; raise optimizer_trace_max_mem_size. The trace only holds the last statement of the same session.