Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE0%
Interpreting EXPLAIN Output (type, key, rows, Extra)

Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

Advanced14 min read•Updated: 2026-09-12
Study Materials

Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

When a SQL query performs poorly, guessing which index to add is ineffective. To optimize queries methodically, you must inspect the Query Execution Plan (QEP) generated by the MySQL Optimizer.

MySQL provides two premier diagnostic tools:

  1. 1
    EXPLAIN: Shows the optimizer's estimated execution plan without running the query.
  2. 2
    EXPLAIN ANALYZE (MySQL 8.0.18+): Executes the query, instrumenting actual runtimes, actual row counts, and memory loop times alongside estimates!

Using Traditional EXPLAIN

Prepend EXPLAIN to any SELECT, INSERT, UPDATE, or DELETE statement:

SQL
EXPLAIN SELECT
c.customer_id,
c.first_name,
o.order_id,
o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'USA' AND o.total_amount > 500;

You can also output the plan in structured JSON format to see detailed cost metrics:

SQL
EXPLAIN FORMAT=JSON SELECT ...;

The Game Changer: EXPLAIN ANALYZE (MySQL 8.0.18+)

Traditional EXPLAIN only shows estimates based on stale table statistics. EXPLAIN ANALYZE actually executes the query, printing a tree breakdown of physical operations:

SQL
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE total_amount > 1000
ORDER BY order_date DESC
LIMIT 10;

Sample EXPLAIN ANALYZE Output:

Output
-> Limit: 10 row(s) (cost=120.45 rows=10) (actual time=0.850..0.852 rows=10 loops=1)
-> Sort: orders.order_date DESC, limit input to 10 row(s) (cost=120.45 rows=100) (actual time=0.849..0.850 rows=10 loops=1)
-> Filter: (orders.total_amount > 1000) (cost=110.25 rows=100) (actual time=0.045..0.750 rows=120 loops=1)
-> Table scan on orders (cost=110.25 rows=1000) (actual time=0.040..0.620 rows=1000 loops=1)

How to Read EXPLAIN ANALYZE Tree Metrics

  1. 1
    Tree Hierarchy: Execution proceeds from the most deeply indented inner node outwards (bottom-up).
  2. 2
    cost: The optimizer's estimated cost score based on disk page reads.
  3. 3
    actual time=start..end:
  • start: Time in milliseconds to retrieve the first row.
  • end: Time in milliseconds to retrieve all rows for that operation.
  1. 1
    rows=X: The actual number of rows output by this step.
  2. 2
    loops=Y: How many times this operation was repeated (e.g., in a nested loop join).
Crucial Rule: Because EXPLAIN ANALYZE executes the query, running it on an unindexed UPDATE or DELETE on a multi-million-row production table will perform the actual modification! Use it primarily on SELECT statements.

Multiple Choice Questions

1. What is the fundamental difference between EXPLAIN and EXPLAIN ANALYZE?

A. EXPLAIN requires root privileges, EXPLAIN ANALYZE does not B. EXPLAIN displays optimizer estimates without execution, while EXPLAIN ANALYZE actually executes the query to report real runtimes C. EXPLAIN ANALYZE only works on views D. EXPLAIN only works with MyISAM Answer: B Explanation: EXPLAIN estimates the execution plan without executing; EXPLAIN ANALYZE runs the query to collect actual execution times and row counts.

2. In which MySQL version was EXPLAIN ANALYZE officially introduced?

A. MySQL 5.7 B. MySQL 8.0.18 C. MySQL 5.6 D. MySQL 4.0 Answer: B Explanation: MySQL introduced EXPLAIN ANALYZE in version 8.0.18 to provide iterator-based execution metrics.

3. In the metric actual time=0.045..0.750, what does 0.045 represent?

A. Total execution time in seconds B. Time in milliseconds taken to return the first matching row C. Number of page reads D. Buffer pool cache hit ratio Answer: B Explanation: The first number indicates the elapsed time in milliseconds to produce the first row of that iterator step.

4. What does FORMAT=JSON provide when appended to EXPLAIN?

A. Compresses the database tables into JSON format B. Generates detailed cost estimates, buffer metrics, and evaluation conditions in structured JSON C. Exports data to a web browser D. Encrypts the query output Answer: B Explanation: EXPLAIN FORMAT=JSON provides a detailed machine-readable breakdown including query cost units and join evaluations.

5. Why must developers exercise caution when running EXPLAIN ANALYZE on DML statements (INSERT, UPDATE, DELETE)?

A. Because it disables primary key constraints B. Because EXPLAIN ANALYZE actually executes the modification in the database! C. Because it resets auto-increment counters D. Because it drops foreign keys Answer: B Explanation: Unlike static EXPLAIN, EXPLAIN ANALYZE runs the actual statement; modifying queries will execute their modifications against table data.

Next Lesson

Interpreting EXPLAIN Output (type, key, rows, Extra)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Full-Text Search Indexes (MATCH ... AGAINST)Interpreting EXPLAIN Output (type, key, rows, Extra)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext