Compare MySQL Query Plans Before and After an Index

Use EXPLAIN ANALYZE to measure real row counts and timing per step, then prove an index actually changed the plan instead of guessing.

~25 mindatabasemysqlperformance

Task

Capture the execution plan of a slow query, add a candidate index, and quantify the improvement with before-and-after evidence.

When to use this

A dashboard query takes eight seconds. Someone proposes an index, but the last three proposed indexes changed nothing because the optimizer kept the old plan.

Prerequisites

MySQL 8.0.18 or newer for EXPLAIN ANALYZE
A slow query with known filter and join columns
Freedom to add and drop an index on a staging copy

Steps

1. Capture the baseline plan with actual timing and row counts.

mysql -e "EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.created_at >= '2026-10-01' AND o.status = 'shipped'\\G" app

Expected output: The plan prints per-step actual time and rows, for example full scan on orders reading 2.4M rows before the filter.

2. Read the plan to see which step dominates and what the optimizer estimated.

mysql -e "EXPLAIN FORMAT=JSON SELECT o.id FROM orders o WHERE o.created_at >= '2026-10-01' AND o.status = 'shipped'\\G" app | grep -E "rows_examined|cost|access_type" | head -8

Expected output: Estimated rows per access type print; a large gap between estimated and actual rows flags statistics the optimizer is misjudging.

3. Create the candidate composite index in the order the query filters.

mysql -e "ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);" app && mysql -e "ANALYZE TABLE orders;" app

Expected output: The index is created and table statistics refreshed so the optimizer sees the new option immediately.

4. Re-run EXPLAIN ANALYZE and compare step by step.

mysql -e "EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.created_at >= '2026-10-01' AND o.status = 'shipped'\\G" app | head -20

Expected output: The new plan shows index access on idx_status_created with actual rows around 40k instead of 2.4M, and total time drops accordingly.

5. Keep or drop the index based on measured benefit, and check write cost.

mysql -e "SELECT index_name, stat_value FROM mysql.innodb_index_stats WHERE table_name = 'orders' AND stat_name = 'size';" app

Expected output: Index sizes print, letting you weigh the read improvement against per-index write amplification before making it permanent.

Troubleshooting

Problem

Optimizer ignores the new index

Solution

Column order in the composite index does not match the predicates; put equality columns first and range columns last, then re-run ANALYZE TABLE.

Problem

EXPLAIN ANALYZE itself is slow

Solution

The statement really executes, so a pathological query costs a real run; wrap it in a read replica or limit rows during diagnosis.

Problem

Plan improves on staging but not production

Solution

Data distributions differ; run ANALYZE TABLE on production during a low-traffic window so its statistics reflect reality, then re-check.

Limitations

EXPLAIN ANALYZE executes the query once, so timings include one warm-cache run and are not a benchmark.
Composite index order is query-specific; an index that helps this query may be dead weight for others, hence the size check.

Estimates lie; actuals testify

Plain EXPLAIN shows the plan the optimizer intends, with estimated row counts that are often wrong by an order of magnitude. EXPLAIN ANALYZE runs the query and reports what actually happened per step, which is the only honest way to prove an index helped.

-- Before: full scan, 2.4M rows
EXPLAIN ANALYZE SELECT o.id FROM orders o
WHERE o.created_at >= '2026-10-01' AND o.status = 'shipped';
-- -> Table scan on o  (actual rows=2.4M, time=7900 ms)

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
ANALYZE TABLE orders;

-- After: index range, 40k rows
EXPLAIN ANALYZE SELECT o.id FROM orders o
WHERE o.created_at >= '2026-10-01' AND o.status = 'shipped';
-- -> Index range scan on idx_status_created  (actual rows=40k, time=45 ms)

Equality first, range last

A composite index serves the query only when its column order matches predicate shape. status is equality, created_at is a range, so (status, created_at) lets the index seek directly to the shipped slice and range within it. Reversing the order forces a wider range scan and the optimizer rightly refuses it.

Every index is a write tax

The read-side win is only half the ledger. Each index adds work to every INSERT and UPDATE on the table. After proving the plan change, check innodb_index_stats sizes and drop indexes that never change a plan; dead indexes are invisible drag on writes.