Reading a Slow MySQL Query Plan with EXPLAIN ANALYZE

A query can be slow because it reads too many rows, repeats an operation many times or waits on work outside the part you measured. EXPLAIN ANALYZE helps reveal how MySQL actually executed a statement, but it is not a cost-free preview.

Start with a representative SELECT

Capture the query and the parameters from a slow request, after removing private values. Run analysis against a safe environment or a read-only SELECT whose workload is acceptable. Unlike ordinary EXPLAIN, EXPLAIN ANALYZE executes the statement to obtain actual timing and row counts. Do not point it at a destructive statement unless you have intentionally managed its effects.

EXPLAIN ANALYZE
SELECT order_id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Replace the table, columns and filter with your real query. The MySQL EXPLAIN reference describes its tree output and execution behavior.

Compare estimates with what happened

Read from the leaves of the plan upward. Compare estimated rows with actual rows, and note how many times an iterator looped. A small lookup repeated thousands of times can dominate a query. Large estimate differences can point to stale statistics, skewed values or predicates the optimizer cannot estimate well.

Use the plan to form a specific hypothesis: perhaps a composite index should start with the filtered column, or a sort is processing far more rows than expected. Verify that hypothesis with a changed query or index in staging, measuring representative parameters.

Measure the whole request too

Execution time in a local test does not include every production wait, cache state or concurrent workload. Compare application latency, database metrics and query plans together. Avoid adding indexes solely because a column appears in a filter; write cost and storage matter, and column order affects composite indexes.

Keep the original plan and the test conditions. That makes it possible to tell whether an optimization improved the real workload rather than one convenient sample.