What EXPLAIN reveals
EXPLAIN asks MySQL to describe how it plans to execute a statement. For a SELECT query, output can show table access order, possible and chosen keys, estimated rows, filtering, and extra operations. The exact output format varies by MySQL version and EXPLAIN format. It is a diagnostic tool: it does not prove that a query is fast or slow by itself. Interpret the plan alongside latency, rows examined, data size, and workload frequency.
EXPLAIN
SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;Review whether the chosen key matches the filter and ordering, whether MySQL estimates a reasonable number of rows, and whether it expects extra sorting or temporary work. A full table scan is not automatically a bug: if the table is small or the query needs most rows, scanning can be cheaper than traversing an index and fetching many table records.
Read estimates in context
The rows estimate is an optimizer estimate, not necessarily an exact count. MySQL uses statistics and data distribution to estimate selectivity. If estimates are far from observed rows, the optimizer may choose a poor plan. Skewed distributions, correlated columns, stale statistics, or parameter differences can contribute. Compare plans for representative predicates rather than one convenient literal. Avoid forcing an index as the first response; hints can lock in a poor choice as the data changes.
The key column indicates an index chosen for access, but an index name alone does not establish that the result is efficient. Consider how many rows are read, whether the query is covered, and whether a sort or temporary operation is required. EXPLAIN FORMAT=JSON can provide additional structural detail. Use the format supported by the deployed version and keep output for before-and-after comparisons.
EXPLAIN ANALYZE executes the statement
Supported MySQL 8.0 versions provide EXPLAIN ANALYZE, which executes the statement and reports actual timing and row information alongside the plan. Because it executes the statement, do not casually use it on a write query, production workload, or expensive statement without understanding the effects. Use a safe read query in a representative environment, and be aware that measurement itself has overhead. Compare actual rows and loops with estimates to identify where the plan differs from expectations.
A plan is only one part of diagnosis. Check whether slow time comes from waiting on locks, network transfer, application processing, or disk I/O. A query may have an acceptable plan but return millions of rows to an application that only needs a page. Measure end-to-end behavior and reduce the result shape where possible. See pagination and optimization workflow.
Use plans to improve queries
Begin with the exact statement, schema, indexes, and approximate data volume. Run EXPLAIN and identify a specific concern: an unexpected full scan, excessive estimated rows, a join order with poor selectivity, or a filesort for a high-volume request. Then make one change—such as adding a targeted index, rewriting a predicate, or reducing output columns—and compare results. Verify correctness as well as speed. A faster query that returns different rows is not an optimization.
Keep SQL predicates compatible with index access where practical. Applying functions to columns, comparing unlike types, or leading-wildcard LIKE patterns can limit common B-tree access. These are diagnostic clues, not absolute laws: functional indexes, optimizer transformations, and data distribution can change the outcome. Validate every claim against the actual plan for the target MySQL release.
Common pitfalls
Do not compare plans from different schema versions without recording index definitions and server settings. Do not equate “Using filesort” with disk sorting or guaranteed slowness; it describes a sorting operation, and its cost depends on rows and resources. Do not assume a selected index is beneficial if it reads most of the table. Avoid running EXPLAIN ANALYZE during a busy period without assessing its workload. Keep optimizer statistics and query inputs in mind when results differ between environments.
EXPLAIN is most valuable when used as part of a measured feedback loop: inspect, form a hypothesis, change one thing, and verify. Continue with index design, joins, and performance tuning.