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

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
EXPLAIN: Shows the optimizer's estimated execution plan without running the query.
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:

1EXPLAIN SELECT 
2    c.customer_id, 
3    c.first_name, 
4    o.order_id, 
5    o.total_amount
6FROM customers c
7JOIN orders o ON c.customer_id = o.customer_id
8WHERE c.country = 'USA' AND o.total_amount > 500;

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

1EXPLAIN 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:

1EXPLAIN ANALYZE 
2SELECT * FROM orders 
3WHERE total_amount > 1000 
4ORDER BY order_date DESC 
5LIMIT 10;

Sample EXPLAIN ANALYZE Output:

1-> 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
Tree Hierarchy: Execution proceeds from the most deeply indented inner node outwards (bottom-up).
2
cost: The optimizer's estimated cost score based on disk page reads.
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
rows=X: The actual number of rows output by this step.
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.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental difference between EXPLAIN and EXPLAIN ANALYZE?

Question 2

In which MySQL version was EXPLAIN ANALYZE officially introduced?

Question 3

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

Question 4

What does FORMAT=JSON provide when appended to EXPLAIN?

Question 5

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

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 Lesson	Next 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.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

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

Execution Plan Analysis with EXPLAIN & EXPLAIN ANALYZE

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

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
EXPLAIN: Shows the optimizer's estimated execution plan without running the query.
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:

1EXPLAIN SELECT 
2    c.customer_id, 
3    c.first_name, 
4    o.order_id, 
5    o.total_amount
6FROM customers c
7JOIN orders o ON c.customer_id = o.customer_id
8WHERE c.country = 'USA' AND o.total_amount > 500;

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

1EXPLAIN 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:

1EXPLAIN ANALYZE 
2SELECT * FROM orders 
3WHERE total_amount > 1000 
4ORDER BY order_date DESC 
5LIMIT 10;

Sample EXPLAIN ANALYZE Output:

1-> 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
Tree Hierarchy: Execution proceeds from the most deeply indented inner node outwards (bottom-up).
2
cost: The optimizer's estimated cost score based on disk page reads.
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
rows=X: The actual number of rows output by this step.
2
loops=Y: How many times this operation was repeated (e.g., in a nested loop join).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental difference between EXPLAIN and EXPLAIN ANALYZE?

Question 2

In which MySQL version was EXPLAIN ANALYZE officially introduced?

Question 3

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

Question 4

What does FORMAT=JSON provide when appended to EXPLAIN?

Question 5

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

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 Lesson	Next 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.

Previous Next