MySQL EXPLAIN and EXPLAIN ANALYZE: Read Query Plans

Use MySQL EXPLAIN to see the optimizer’s plan for a statement. Use EXPLAIN ANALYZE when you also need actual row counts and execution timings. The key difference is that EXPLAIN ANALYZE runs the statement it analyzes. See the MySQL 8.4 EXPLAIN reference.

Inspect the estimated plan with EXPLAIN

This query asks for actors with a particular last name:

EXPLAIN
SELECT actor_id, first_name, last_name
FROM sakila.actor
WHERE last_name = 'SMITH';

EXPLAIN returns plan information such as the tables and indexes MySQL expects to use and an estimated row count. It does not run the SELECT and return its result rows. The estimates help you see how MySQL plans to retrieve and join data.

MySQL can display plans in traditional, JSON, or tree form:

EXPLAIN FORMAT=JSON
SELECT actor_id, first_name, last_name
FROM sakila.actor
WHERE last_name = 'SMITH';

Measure actual execution with EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT actor_id, first_name, last_name
FROM sakila.actor
WHERE last_name = 'SMITH';

MySQL executes the query and prints a tree plan with estimates alongside actual rows, loops, and timing. Compare estimated rows with actual rows: a large mismatch can indicate that the optimizer’s assumptions about a filter or join are inaccurate. The plan’s timing lines describe iterator work; parent iterator times can include child work, so do not add every nested time together.

EXPLAIN ANALYZE supports SELECT, TABLE, and multi-table UPDATE or DELETE statements. Because it executes the statement, an analyzed UPDATE or DELETE can change data. Use it with a read-only query when you only want to measure a plan, or analyze modifying statements only when their execution is intended and safe.

Use the plan to investigate indexes

Look for operations such as table scans, index lookups, and joins, then compare them with the query’s filters and available indexes. A table scan is not automatically wrong; it can be reasonable for small tables or queries that return much of the table. For index design, see MySQL CREATE INDEX. For query syntax and filtering, see MySQL SELECT.