MySQL EXPLAIN ANALYZE Complete Guide: Reading Plans and Query Optimization

1. Introduction

Essential “Execution Plan” for Optimizing Database Performance

In web applications and business systems, database performance is a critical factor that directly impacts the overall response time. Especially when using MySQL, understanding the “Execution Plan” is essential for measuring query efficiency. The traditional EXPLAIN command, which has been used for a long time, displays the plan before executing the SQL statement and provides developers with important clues.

“EXPLAIN ANALYZE” Introduced in MySQL 8.0

The EXPLAIN ANALYZE feature, newly introduced starting from MySQL 8.0.18, is a powerful tool that advances the traditional EXPLAIN one step further. While the previous EXPLAIN was limited to “theoretical plans,” EXPLAIN ANALYZE actually executes the query and provides “actual measured data” such as execution time and number of rows. This enables more accurate identification of bottlenecks and verification of the effects of query optimization.

Why EXPLAIN ANALYZE Is Important

For example, the order of JOINs, the presence or absence of indexes, and the effectiveness of filters can have a significant impact on execution time. By using EXPLAIN ANALYZE, you can visually inspect the execution results of the SQL statement to determine which parts are inefficient and where optimization is needed. This is an indispensable analysis technique, especially in environments handling large-scale data or complex queries.

Purpose of This Article and Target Audience

In this article, we will explain step by step from the basic usage of MySQL’s EXPLAIN ANALYZE to interpreting the output results and practical optimization techniques. The target readers are developers and infrastructure personnel who use MySQL on a daily basis, as well as engineers interested in performance improvement. We will include term explanations and specific examples to make it easy to understand even for beginners, so please feel free to read on.

2. EXPLAIN and EXPLAIN ANALYZE Differences

EXPLAIN Role and Basic Usage

MySQL’s EXPLAIN is an analysis tool for learning in advance how SQL statements (especially SELECT statements) will be executed. You can check the execution plan, including whether indexes are used, the order of table joins, search ranges, and more.

For example, use it like this.

EXPLAIN SELECT * FROM users WHERE age > 30;

When you execute a command like this, MySQL displays in tabular form how the query is planned to be processed without actually executing it. The output includes the used index (key), access method (type), estimated number of rows (rows), and more.

EXPLAIN ANALYZE Role and Features

On the other hand, EXPLAIN ANALYZE, introduced in MySQL 8.0.18 and later, is a new feature that actually executes the query and displays the execution plan based on measured values. This allows you to check “actual processing time” and “actual number of rows processed,” which were not visible with the conventional EXPLAIN.

An execution example is as follows:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

With this command, MySQL actually executes the query and returns output with information like the following.

• Time taken for each step of the execution plan (e.g. 0.0022 sec)
• Actual number of rows read (rows)
• Nested structure of the processing (easy to visualize using TREE format)

Summary of Main Differences Between the Two

Which One Should You Use?

• Use EXPLAIN if you want to quickly check the query structure
• Use EXPLAIN ANALYZE if you want to specifically understand the query’s processing time or execution cost

This is the basic way to distinguish between them. Especially in performance tuning scenarios, EXPLAIN ANALYZE enables optimization based on “actual conditions” rather than “predictions,” making it a highly effective tool.

3. EXPLAIN ANALYZE Output Format

There are 3 Types of Output Formats: TRADITIONAL, JSON, and TREE

MySQL’s EXPLAIN ANALYZE can output results in different formats depending on the purpose or objective. Since MySQL 8.0, the following three types of output formats are available.

Let’s take a closer look at the differences in each format.

TRADITIONAL Format (Default)

The TRADITIONAL format outputs in a style similar to the conventional EXPLAIN, allowing you to check the execution plan in table form. If you use EXPLAIN ANALYZE as is, it basically outputs in this format.

Output Example (Excerpt):

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

• cost: Estimated Cost
• actual time: Actual Time
• rows: Estimated Number of Processed Rows (Before Execution)
• loops: Number of Loops (Especially Important for JOINs)

The TRADITIONAL format is easy for humans to understand at a glance and is suitable for beginners or quick checks.

JSON Format

The JSON format is more detailed and easier to handle from programs. It is structured, with information for each node output as nested objects.

Execution Command:

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

Part of the Output Example (Formatted):

{
  "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 visually hard to read, but it is very convenient when parsing the data and incorporating it into tools or dashboards.

TREE Format (Readability and Structure Visualization)

The TREE format displays the query’s processing structure as a tree structure, allowing visual grasp of the processing order for JOINs and subqueries.

Execution Command:

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

Output Example (Simplified):

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

For complex queries, nesting is displayed like this:

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

The TREE format is particularly suitable for queries with many JOINs or complex nesting to understand the processing flow.

Which Format Should You Use?

Choose the format according to your purpose and check the execution plan in the most readable and analyzable style.

4. How to Read Execution Plans

Why Do You Need to Read Execution Plans?

MySQL queries can vary greatly in processing speed depending on the amount of data and the presence or absence of indexes.By correctly interpreting the execution plan output using EXPLAIN ANALYZE, you can objectively determine where there is waste and where improvements should be made. In particular, for queries that handle large amounts of data or complex join processing, this skill becomes the key to performance tuning.

Basic Structure of Execution Plans

The output of EXPLAIN ANALYZE includes information like the following (explained based on the TRADITIONAL format):

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

This single line contains multiple important pieces of information.

How to Interpret Important Items

1. cost and actual time

• cost is MySQL’s internal calculation of “estimated cost,” used for relative evaluation of execution speed.
• actual time is the actual time taken, which is more important for performance analysis.

For example:

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

If the estimate and actual measurements are nearly identical like this, the accuracy of the execution plan is considered high. Conversely, if there is a large difference, the accuracy of the statistical information may be low.

2. rows and actual rows

• rows is the number of rows MySQL predicts it will read
• actual rows is the actual number of rows read (included in parentheses in TRADITIONAL format)

If there is a large discrepancy between these two, it is necessary to update the statistical information or review the index design.

3. loops

loops=1 means it is executed only once, but with JOINs or subqueries, it can increase to loops=10 or loops=1000. The larger this value, the more likely it is that processing is becoming heavy due to nested loops.

Understanding the Nested Structure of Execution Plans

When there are JOINs with multiple tables, the execution plan is displayed in a tree structure (especially prominent in TREE format).

Example:

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

Issues

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

Remedies

• Add an index to users.age and perform filtering first to reduce the number of join targets.

How to Identify Performance Bottlenecks?

By focusing on points like the following, it becomes easier to find bottlenecks.

• Nodes with long actual time and many rows: They occupy most of the processing time
• Locations where full table scans are occurring: Possibility of unused indexes
• Parts with many loops: Indicates inefficiency in JOIN order or nesting
• Discrepancy between rows and actual rows: Inaccuracy in statistics or excessive data access

It is important to utilize these in the “query optimization” introduced in the next chapter.

5. Practical Examples of Query Optimization

What is Query Optimization?

Query optimization refers to the process of reviewing SQL statements to execute them more efficiently. Based on how MySQL processes queries internally (execution plan), we perform tasks such as adding indexes, changing join orders, and reducing unnecessary processing.

Here, using EXPLAIN ANALYZE, we will introduce how queries can actually be improved, with specific examples.

Example 1: Speedup Using Indexes

Query Before Optimization

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

Part of the Execution Plan

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

Issues

• As indicated by Table scan, a full table scan is being performed, which causes delays when the amount of data is large.

Countermeasure: Adding 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)

Results

• Execution time is significantly reduced.
• By using the index, unnecessary full table scans are avoided.

Example 2: Optimizing Join Order

Query Before Optimization

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

Part of the Execution Plan

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

Issues

• Both tables are subject to full table scans, resulting in high join costs.

Countermeasures

• By adding an index to users.age and performing the filtering first, reduce the number of join targets.

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

Results

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

Example 3: Reviewing Subqueries

Query Before Optimization

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

Issues

• The subquery is evaluated for each execution, leading to performance degradation.

Countermeasure: Rewrite as JOIN

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

Results

• In the execution plan, the JOIN is optimized, making it easier to use indexes.

Comparing Before and After is Key

EXPLAIN ANALYZE allows you to confirm the effects of optimization with “actual measurements”. By examining changes in execution time and row counts before and after improvements, you can achieve reliable tuning without just assuming it was done.

Notes on Optimization

• Excessive addition of indexes can sometimes be counterproductive (degradation in INSERT/UPDATE performance).
• Execution plans depend on data volume and statistics, so verification is necessary for each environment.
• Not everything speeds up with one optimization. Bottleneck analysis is the top priority.

6. Notes and Best Practices

EXPLAIN ANALYZE Usage Notes

EXPLAIN ANALYZE is a very powerful tool, but if not used correctly, it can lead to misunderstandings or issues. By keeping the following notes in mind, you can safely and effectively analyze and optimize queries.

1. Do Not Execute Casually in Production Environments

EXPLAIN ANALYZE actually executes the query, so if you mistakenly use it with update-type SQL statements (INSERT/UPDATE/DELETE, etc.), there is a risk of data being modified.

• Basically, avoid using it with anything other than SELECT statements.
• Avoid executing on production data; perform it in a verification environment as a principle.

2. Consider the Impact of Caching

MySQL may return data from the cache when the same query is repeated. Therefore, the execution time of EXPLAIN ANALYZE may differ from the actual situation.Countermeasures:

• Clear the cache before execution (RESET QUERY CACHE;).
• Execute multiple times and take the average value without variation.

3. Updating Statistics is a Prerequisite

MySQL creates execution plans based on index and column statistics. If these are outdated, neither EXPLAIN nor ANALYZE will return correct information.Especially after a large number of INSERT/DELETE operations, update the statistics with ANALYZE TABLE.

ANALYZE TABLE users;

4. Indexes Are Not Omnipotent

There are many cases where adding indexes can speed things up, but too many indexes can slow down write operations as a side effect.

Also, the choice between composite indexes and single indexes is important. Design appropriate indexes according to usage frequency and search conditions.

5. Do Not Judge Solely by Execution Time

The results of EXPLAIN ANALYZE only show the performance of the individual query. When viewed in the entire application, other factors such as network latency or backend processing may become bottlenecks.

Therefore, analysis with an understanding of the query’s role within the overall design is required.

Summary of Best Practices

7. Frequently Asked Questions (FAQ)

Q1. From which version can EXPLAIN ANALYZE be used?

A.
MySQL’s EXPLAIN ANALYZE is a feature introduced in version 8.0.18 and later. It is not supported in versions prior to 8.0, so you need to check the MySQL version when using it.

Q2. When executing EXPLAIN ANALYZE, will the data be modified?

A.
Basically, EXPLAIN ANALYZE performs analysis without executing the query, so if it is a SELECT statement, no data modification will occur.
However, if you mistakenly use INSERT or UPDATE, etc., the data will be modified just like with a normal query.
*For safety, it is recommended to execute the analysis in a test database for verification.

Q3. Isn’t just EXPLAIN enough?

A.EXPLAIN is sufficient for checking the “estimated” execution plan, but it does not output actual measurements such as how much time it actually took or how many rows were processed.
For serious query tuning or when effect verification is needed, EXPLAIN ANALYZE is more useful.

Q4. How accurate are the output values like “loops” or “actual time”?

A.The values for actual time and loops are real information measured internally by MySQL during execution. However, they may vary slightly depending on the OS conditions, cache state, server load, and so on.
Therefore, rather than judging based on a single run, it is ideal to observe trends over multiple executions.

Q5. What exactly does the “cost” in the execution result represent?

A.cost is an estimated value based on MySQL’s internal cost model and is an indicator that relatively evaluates CPU and I/O costs, etc.. Since the unit is not real time (seconds), it is used solely to assess relative efficiency.
For example, if there is (cost=0.3) and (cost=2.5), it means the latter is estimated to be a heavier process.

Q6. What is convenient about using JSON format or TREE format?

A.

• JSON format: It is structured and easy to parse with programs. It is easy to utilize in automation tools or dashboards.
• TREE format: The processing flow and nested structures are visually easy to understand, and the order of complex queries or JOINs can be seen at a glance.

Since each has different uses, the key is to choose based on your purpose.

Q7. What should I do if I can’t improve by looking at the execution plan?

A.
It would be good to consider additional measures like the following.

• Redesigning indexes (composite indexes or covering indexes)
• Rewriting the query itself (subqueries to JOINs, deleting unnecessary SELECT columns)
• Utilizing views or temporary tables
• Reviewing MySQL configuration (buffer size, etc.)

Tuning rarely completes with just one method, so it is important to think about it comprehensively.