MySQL EXPLAIN ANALYZE Explained: Read Execution Plans & Optimize Queries (8.0 Guide)

1. Introduction

Execution Plans: Essential for Database Performance Optimization

In web applications and business systems, database performance is a critical factor that directly affects overall response time. When using MySQL in particular, understanding the “execution plan” is essential for evaluating query efficiency. The traditional EXPLAIN command displays the execution plan before running an SQL statement and has long provided developers with valuable insights.

“EXPLAIN ANALYZE” Introduced in MySQL 8.0

Introduced in MySQL 8.0.18, EXPLAIN ANALYZE is a powerful enhancement of the traditional EXPLAIN. While EXPLAIN only provided a “theoretical plan,” EXPLAIN ANALYZE actually executes the query and returns measured data such as execution time and processed row counts. This enables more accurate identification of bottlenecks and validation of query optimization results.

Why EXPLAIN ANALYZE Matters

For example, JOIN order, index usage, and filtering conditions significantly affect execution time. By using EXPLAIN ANALYZE, you can visually confirm how an SQL statement performs and determine where inefficiencies exist and what should be optimized. This is especially indispensable when working with large datasets or complex queries.

Purpose of This Article and Target Audience

This article explains everything from the basics of MySQL’s EXPLAIN ANALYZE to interpreting its output and applying practical optimization techniques. It is intended for developers and infrastructure engineers who regularly use MySQL, as well as engineers interested in performance tuning. To ensure clarity even for beginners, we include terminology explanations and concrete examples throughout.

2. Differences Between EXPLAIN and EXPLAIN ANALYZE

The Role and Basic Usage of EXPLAIN

MySQL’s EXPLAIN is an analysis tool used to understand in advance how an SQL statement (especially a SELECT statement) will be executed. It allows you to confirm execution plans such as index usage, join order, and search ranges.

For example:

EXPLAIN SELECT * FROM users WHERE age > 30;

When this command is executed, MySQL does not actually run the query, but instead displays how it plans to process it in tabular form. The output includes information such as the index used (key), access method (type), and estimated number of rows (rows).

The Role and Features of EXPLAIN ANALYZE

In contrast, EXPLAIN ANALYZE, introduced in MySQL 8.0.18, executes the query and displays the execution plan based on actual measured values. This makes it possible to confirm details that were not visible in traditional EXPLAIN, such as actual processing time and the number of rows actually processed.

Example:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

This command executes the query and returns output including:

• Execution time for each step of the plan (e.g., 0.0022 sec)
• The actual number of rows read (rows)
• The processing structure (easily visualized using TREE format)

Summary of Key Differences

Which One Should You Use?

• Use EXPLAIN when you want to quickly check query structure.
• Use EXPLAIN ANALYZE when you need concrete details about execution time and query cost.

Especially in performance tuning scenarios, EXPLAIN ANALYZE enables optimization based on real execution data rather than estimates, making it an extremely powerful tool.

3. Output Formats of EXPLAIN ANALYZE

Three Output Formats: TRADITIONAL, JSON, and TREE

MySQL’s EXPLAIN ANALYZE can output results in different formats depending on your purpose. In MySQL 8.0 and later, the following three formats are available.

Let’s take a closer look at the differences.

TRADITIONAL Format (Default)

TRADITIONAL output is similar to the classic EXPLAIN style and lets you review execution plans in a familiar form. If you run EXPLAIN ANALYZE without specifying a format, the result is generally shown in this format.

Example output (excerpt):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost: estimated cost
• actual time: measured time
• rows: estimated number of processed rows (before execution)
• loops: loop count (especially important for JOIN)

TRADITIONAL format is easy for humans to scan and understand, making it suitable for beginners and quick checks.

JSON Format

JSON format is more detailed and easier to handle programmatically. The output is structured, with each node represented as a nested object.

Command:

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

Part of the output (pretty-printed):

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

This format is less visually readable, but it is extremely convenient when you want to parse the data and feed it into analysis tools or dashboards.

TREE Format (Readable and Great for Visualizing Structure)

TREE format displays the query execution structure as a tree, making it easier to understand JOIN and subquery processing order.

Command:

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

Example output (simplified):

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

For complex queries, nesting can appear like this:

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

TREE format is especially useful for queries with many JOINs or complex nesting, where you need to grasp the processing flow.

Which Format Should You Use?

Choose the format that best fits your goal, and review the execution plan in the most readable and analyzable style.

4. How to Interpret Execution Plans

Why You Need to Read Execution Plans

MySQL query performance can vary greatly depending on data volume and index availability. By correctly interpreting the execution plan output from EXPLAIN ANALYZE, you can objectively identify where work is being wasted and what should be improved. This skill is a cornerstone of performance tuning, especially for queries that handle large datasets or complex joins.

Basic Structure of an Execution Plan

The output of EXPLAIN ANALYZE includes information such as the following (explained here based on TRADITIONAL-style output):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

This single line contains multiple important fields.

How to Read Key Fields

1. cost vs. actual time

• cost is an internal estimate computed by MySQL and is used for relative evaluation.
• actual time reflects real elapsed time and is more important for performance analysis.

For example:

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

If estimates and measurements closely match, the execution plan is likely accurate. If the gap is large, table statistics may be inaccurate.

2. rows vs. actual rows

• rows is the number of rows MySQL predicts it will read.
• actual rows is the number of rows actually read (included in parentheses in TRADITIONAL-style output).

If there is a large discrepancy, you may need to refresh statistics or reconsider index design.

3. loops

If loops=1, the step runs once. With JOINs or subqueries, you may see loops=10 or loops=1000. The larger the value, the more likely nested loops are causing heavy processing.

Understand the Nested Structure of Execution Plans

When multiple tables are joined, the execution plan is shown as a tree (especially clear in TREE format).

Example:

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

Issue

• Both tables are fully scanned, resulting in a high join cost.

Countermeasure

• Add an index on users.age and filter earlier to reduce the join workload.

How to Identify Performance Bottlenecks

Focusing on the following points makes bottlenecks easier to find:

• Nodes with long actual time and many rows: These consume most of the execution time
• Places where a full table scan occurs: Likely missing or unused indexes
• Steps with many loops: Indicates inefficient JOIN order or nesting
• Large gaps between rows and actual rows: Suggests inaccurate statistics or excessive data access

Use these insights as the foundation for the “Query Optimization” techniques introduced in the next section.

5. Practical Query Optimization Examples

What Is Query Optimization?

Query optimization refers to reviewing and improving SQL statements so they can be executed more efficiently. Based on how MySQL processes queries internally (execution plans), you apply improvements such as adding indexes, adjusting join order, and eliminating unnecessary processing.

Here, we demonstrate how to improve queries using EXPLAIN ANALYZE with concrete examples.

Example 1: Speed Improvement Using Indexes

Before Optimization

SELECT * FROM users WHERE email = 'example@example.com';

Execution Plan (Excerpt)

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

Issue

• The output shows Table scan, meaning a full table scan is performed. With large datasets, this leads to significant delays.

Solution: Add an Index

CREATE INDEX idx_email ON users(email);

Execution Plan After Optimization

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

Result

• Execution time significantly reduced.
• Full table scan avoided by using the index.

Example 2: Optimizing Join Order

Before Optimization

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Execution Plan (Excerpt)

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

Issue

• Both tables are fully scanned, resulting in high join costs.

Solution

• Add an index on users.age and filter first to reduce the join target size.

CREATE INDEX idx_age ON users(age);

Execution Plan After Optimization

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

Result

• JOIN targets are filtered first, reducing overall processing load.

Example 3: Revising a Subquery

Before Optimization

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Issue

• The subquery may be evaluated repeatedly, degrading performance.

Solution: Rewrite as a JOIN

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

Result

• The execution plan is optimized for JOIN processing, and indexes are more likely to be used.

The Importance of Before/After Comparison

Using EXPLAIN ANALYZE, you can verify optimization results with actual measured values. By comparing execution time and row counts before and after improvements, you ensure that tuning efforts are based on real performance gains rather than assumptions.

Important Considerations in Optimization

• Adding too many indexes can be counterproductive (slower INSERT/UPDATE performance).
• Execution plans depend on data volume and statistics, so validation is required per environment.
• One optimization rarely solves everything. Bottleneck analysis comes first.

6. Precautions and Best Practices

Important Notes When Using EXPLAIN ANALYZE

Although EXPLAIN ANALYZE is extremely powerful, improper use can lead to misunderstandings or even operational risks. Keeping the following points in mind ensures safe and effective query analysis.

1. Avoid Running Carelessly in Production

Because EXPLAIN ANALYZE actually executes the query, mistakenly using it with modification statements (INSERT/UPDATE/DELETE) can change data.

• In general, only use it with SELECT statements.
• Prefer running it in a staging or testing environment rather than production.

2. Consider the Impact of Caching

MySQL may return results from cache if the same query is executed repeatedly. As a result, execution time reported by EXPLAIN ANALYZE may differ from real-world behavior.

Countermeasures:

• Clear the cache before execution (RESET QUERY CACHE;).
• Run multiple times and evaluate based on average values.

3. Keep Statistics Up to Date

MySQL builds execution plans based on table and index statistics. If statistics are outdated, both EXPLAIN and EXPLAIN ANALYZE may provide misleading information.

After large INSERT or DELETE operations, update statistics using ANALYZE TABLE.

ANALYZE TABLE users;

4. Indexes Are Not a Silver Bullet

While indexes often improve performance, too many indexes slow down write operations.

Choosing between composite indexes and single-column indexes is also important. Design indexes carefully based on query patterns and usage frequency.

5. Do Not Judge Solely by Execution Time

Results from EXPLAIN ANALYZE reflect only the performance of a single query. In real applications, network latency or backend processing may be the actual bottleneck.

Therefore, analyze queries within the context of the entire system architecture.

Best Practices Summary

7. Frequently Asked Questions (FAQ)

Q1. From which version is EXPLAIN ANALYZE available?

A.
MySQL’s EXPLAIN ANALYZE was introduced in version 8.0.18 and later. It is not supported in versions prior to 8.0, so you should verify your MySQL version before using it.

Q2. Can running EXPLAIN ANALYZE modify data?

A.
EXPLAIN ANALYZE executes the query internally.
When used with a SELECT statement, it does not modify data.

Therefore, when used with a SELECT statement, it does not modify data.

However, if you mistakenly use it with INSERT, UPDATE, or DELETE, the data will be modified just as with a normal query.

For safety, it is recommended to run analyses in a test or staging database rather than in production.

Q3. Isn’t EXPLAIN alone sufficient?

A.
EXPLAIN is sufficient for reviewing the “estimated” execution plan. However, it does not provide measured values such as actual execution time or actual row counts.

If you need serious query tuning or want to verify optimization effects, EXPLAIN ANALYZE is more useful.

Q4. How accurate are values like “loops” and “actual time”?

A.
Values such as actual time and loops are real execution metrics measured internally by MySQL. However, they may fluctuate slightly depending on OS conditions, cache state, and server load.

For this reason, do not rely on a single measurement. Instead, run the query multiple times and evaluate trends.

Q5. What exactly does “cost” represent?

A.
cost is an estimated value calculated by MySQL’s internal cost model. It reflects a relative evaluation of CPU and I/O costs. It is not expressed in seconds.

For example, if you see (cost=0.3) and (cost=2.5), the latter is estimated to be more expensive in relative terms.

Q6. What are the benefits of using JSON or TREE format?

A.

• JSON format: Structured output that is easy to parse programmatically. Useful for automation tools and dashboards.
• TREE format: Makes execution flow and nesting visually clear. Ideal for understanding complex queries and JOIN order.

Choose the format that best fits your purpose.

Q7. What should I do if I cannot improve performance after reviewing the execution plan?

A.
Consider additional approaches such as:

• Redesigning indexes (composite indexes or covering indexes)
• Rewriting queries (subqueries → JOINs, removing unnecessary SELECT columns)
• Using views or temporary tables
• Reviewing MySQL configuration (buffer sizes, memory allocation, etc.)

Performance tuning rarely succeeds with a single technique. A comprehensive and iterative approach is essential.