MySQL EXPLAIN Explained: How to Read Execution Plans and Optimize SQL Queries

1. What Is MySQL EXPLAIN? Why Should You Use It?

What Is EXPLAIN? A Command to Visualize Execution Plans

In MySQL, EXPLAIN is a command used to visualize how an SQL query is executed. It is especially helpful for understanding how data is retrieved in SELECT statements, and it displays the query’s execution plan.

For example, when executing a query such as SELECT * FROM users WHERE age > 30, EXPLAIN allows you to see internal details such as which index MySQL is using and in what order tables are scanned.

The usage is simple — just add EXPLAIN at the beginning of your query.

EXPLAIN SELECT * FROM users WHERE age > 30;

When written this way, multiple columns describing the query execution plan will be displayed. Each item will be explained in detail in the following sections.

Why You Should Use It: Make Slow Query Causes Visible

A common mistake many developers make is assuming that “if the SQL works, there’s no problem.” However, slow query execution can negatively impact overall application performance.

In systems handling large volumes of data, even a single inefficient query can become a bottleneck and put significant load on the server.

That’s where EXPLAIN becomes extremely useful. By reviewing the execution plan, you can clearly see whether a full table scan is being performed or whether indexes are properly utilized.

In other words, using EXPLAIN allows you to identify performance bottlenecks and determine how to optimize them. Index effectiveness, in particular, becomes much clearer when analyzing EXPLAIN output.

SQL Statements Supported by EXPLAIN (SELECT, UPDATE, etc.)

EXPLAIN works not only with SELECT statements but also with the following SQL statements:

• SELECT
• DELETE
• INSERT
• REPLACE
• UPDATE

For example, when running a DELETE statement on a large dataset, if indexes are not used properly, MySQL may perform a full table scan, significantly increasing execution time. To prevent such issues, it is highly effective to check the execution plan with EXPLAIN before running DELETE or UPDATE statements.

Depending on your MySQL version, you may also use EXPLAIN ANALYZE, which provides even more detailed execution information. This will be covered later in the article.

2. Understanding EXPLAIN Output Columns (With Illustrated Image)

List and Explanation of Basic Output Columns

The EXPLAIN output includes the following columns (slightly different depending on MySQL version):

Among these, the four most important columns for performance tuning are type / key / rows / Extra.

How to Read the Four Key Columns: type / key / rows / Extra

1. type (Access Method)

This column indicates how MySQL accesses the table. It directly affects performance.

If type = ALL, it means no index is used and all rows are scanned — the slowest access method. Ideally, you should optimize queries toward ref or const.

2. key (Used Index)

This column displays the name of the index actually used.
If nothing is shown, the query is likely not using an index.

3. rows (Estimated Rows to Scan)

This shows how many rows MySQL estimates it will scan. The larger the number, the longer the execution time tends to be. The goal is to optimize your query so that rows is as close to 1 as possible.

4. Extra (Additional Information)

The Extra column includes additional details such as sorting operations or temporary table usage.

If you see Using temporary or Using filesort, you should review your SQL statement or index design.

[Illustration] Sample EXPLAIN Output

EXPLAIN SELECT * FROM users WHERE age > 30;

In this example, although the index (age_index) exists, it is not actually used, resulting in ALL (full table scan). This indicates room for optimization.

3. Learn by Example: How to Use EXPLAIN and Interpret Results

Example 1: EXPLAIN Output for a Simple SELECT Query (With Explanation)

Let’s start with a simple SELECT query on a single table.

EXPLAIN SELECT * FROM users WHERE age > 30;

Assume the EXPLAIN output looks like this:

Explanation:

• type: ALL → Full table scan. No index is being used.
• key: NULL → No index is actually used.
• rows: 5000 → MySQL estimates it will scan about 5,000 rows.

How to improve:

By adding an index to the age column, you can significantly improve query performance.

CREATE INDEX idx_age ON users(age);

If you run EXPLAIN again, you should see type change to range or ref, confirming that the index is now being used.

Example 2: Analyze EXPLAIN Output for a Query with JOIN

Next, let’s look at an example that JOINs multiple tables.

EXPLAIN
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Example output:

Explanation:

• The users table is doing a full scan (ALL), so this is the part to improve.
• Meanwhile, the orders table uses an index with ref, which is efficient.

Optimization points:

• Adding an index on users.age can speed up scanning the users table.
• The key is to design indexes so the WHERE clause can filter rows before the JOIN.

When Indexes Aren’t Used (Bad Example → Good Example)

Bad example: WHERE clause using a function

SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

With a query like this, the index becomes unusable because the DATE() function transforms the column value, preventing MySQL from using the index efficiently.

Improved example: specify a range without using a function

SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

This enables the index on the created_at column, allowing MySQL to retrieve data efficiently.

Conclusion: Use Real EXPLAIN Examples to Diagnose Performance

By analyzing EXPLAIN output in real queries, you can clearly identify where the bottlenecks are and how to optimize them.

• ALL → Full scan. Consider adding or adjusting indexes.
• key = NULL → Index not used. Needs investigation.
• Extra contains Using temporary → Performance warning.
• Using functions or calculations in conditions can disable index usage.

Keeping these points in mind will help you continuously improve query performance with EXPLAIN.

4. Practical Query Optimization Techniques Based on EXPLAIN Results

Index Design Basics to Avoid “type: ALL”

If EXPLAIN shows type: ALL, it means MySQL is performing a full table scan. This is a very expensive operation, and it becomes a major bottleneck for tables containing thousands to millions of rows.

How to avoid it:

• Add indexes to columns used in the WHERE clause

  CREATE INDEX idx_age ON users(age);

• If you have multiple conditions, consider a composite index

  CREATE INDEX idx_status_created ON orders(status, created_at);

• Avoid LIKE patterns that don’t start with a prefix

  -- Bad example (index won’t work)
  WHERE name LIKE '%tanaka%'

  -- Good example (index may work)
  WHERE name LIKE 'tanaka%'

What “Extra: Using temporary” Means and How to Fix It

If the Extra column shows “Using temporary,” it means MySQL is creating a temporary table internally to process the query. This often occurs when operations such as GROUP BY or ORDER BY cannot be handled by indexes alone, so MySQL must use temporary storage to organize the data manually.

How to fix it:

• Apply indexes to columns used in GROUP BY and ORDER BY

  CREATE INDEX idx_group_col ON sales(department_id);

• Remove unnecessary sorting or GROUP BY from your SQL
• Use LIMIT or subqueries to reduce the target data

Understand What “rows” and “key” Tell You to Improve Performance

The rows column indicates how many rows MySQL predicts it needs to read from the table. For example, a query showing rows = 100000 can significantly impact performance.

When this value is large, you likely need to apply indexes that reduce the number of scanned rows or rewrite your conditions.

On the other hand, the key column shows the index actually used. If it is NULL, that’s a warning that no index is being used.

Optimization checklist:

• If rows is large → Are your filters effective? Are indexes being used properly?
• If key = NULL → Are you using patterns in WHERE/JOIN that prevent index usage?

Make EXPLAIN and Optimization a Habit

To tune queries effectively, the basic approach is to repeat this cycle: write → check with EXPLAIN → improve → check again.

Keep this workflow in mind:

1. Write the query normally
2. Check the execution plan with EXPLAIN
3. Review type, key, rows, and Extra
4. If there is a bottleneck, revise indexes or rewrite the query
5. Run EXPLAIN again to confirm improvements

Query performance is influenced not only by indexes but also by how the query itself is written. Simple comparisons (instead of functions) and straightforward conditions can be surprisingly effective.

5. Visual Analysis with MySQL Workbench Visual EXPLAIN

Check Execution Plans Visually with a GUI Tool

MySQL Workbench is a GUI tool specialized for MySQL administration and development. One of its biggest advantages is that it can visually display execution plans, which are often difficult to read in terminal output.

With Visual EXPLAIN, you can review the following information in a tree structure:

• Access order of each table
• Type of JOIN used
• Index usage status
• Whether a full table scan is occurring
• Data filtering and sorting operations

Because the plan is displayed graphically, even beginners can more easily identify where performance bottlenecks exist.

[With Images] How to Use and Read Visual EXPLAIN (Step-by-Step)

Follow these steps to use Visual EXPLAIN:

1. Launch MySQL Workbench and open your database connection → Make sure the connection is configured in advance.
2. Enter your target query in the SQL editor

SELECT * FROM users WHERE age > 30;

3. Click the “EXPLAIN VISUAL” icon next to the EXPLAIN button → Or right-click and select “Visual Explain” from the menu.
4. The execution plan will be displayed visually When you click each node (table), detailed information such as the following appears:

• Access method (ALL, ref, range, etc.)
• Used index
• Estimated rows (rows)
• Filter conditions and JOIN method

Note:
In Visual EXPLAIN, node colors and icons help highlight heavy operations or inefficient parts.
Pay special attention to nodes highlighted in red, as they typically indicate performance concerns.

Even Beginners Can Easily Find Bottlenecks

Text-based EXPLAIN output can feel overwhelming at first, but Visual EXPLAIN makes problem areas visually stand out.

For example, it becomes easier to identify:

• Tables using type: ALL
• Query blocks showing Using temporary
• Patterns with unnecessary JOINs
• Tables where indexes are not being used

With its GUI interface, you can quickly form optimization hypotheses, and it’s also useful for sharing and reviewing SQL performance within a team.

Visual EXPLAIN is especially valuable for beginner to intermediate SQL users.
If you’re unsure how to interpret EXPLAIN results, try using this feature.

6. Frequently Asked Questions (FAQ)

Q1. When should I use EXPLAIN?

A. You should use EXPLAIN whenever you feel uncertain about a query’s execution speed — especially if a query “feels slow.” It is also useful when you want to verify whether a newly created query is using indexes properly.

By checking the execution plan before deployment, you can identify performance risks early.

Q2. The output shows type = ALL. What should I do?

A. type: ALL means MySQL is performing a full table scan. This is a high-cost operation and can significantly degrade performance, especially on large tables.

Consider the following actions:

• Add indexes to columns used in the WHERE clause
• Avoid functions or operations that disable index usage
• Avoid SELECT * and retrieve only necessary columns

Q3. Is “Using temporary” in the Extra column a problem?

A. Using temporary indicates that MySQL is internally creating a temporary table to process the query. This often occurs with GROUP BY or ORDER BY, and it may increase memory and disk I/O costs.

Possible solutions include:

• Add indexes to columns used in GROUP BY / ORDER BY
• Reduce unnecessary sorting or aggregation
• Use LIMIT or subqueries to reduce the data set

Q4. How do I use Visual EXPLAIN?

A. You can use the official MySQL tool “MySQL Workbench” to visualize EXPLAIN results easily in a GUI. Simply enter your query and click the “Visual Explain” button.

This is particularly recommended for:

• Users who find text-based EXPLAIN output difficult to read
• Those who want to visually understand complex JOINs
• Teams reviewing SQL performance together

Q5. Why isn’t my index being used even though it exists?

A. Even if an index exists, MySQL does not always use it. Indexes may be ignored in cases such as:

• Using functions or expressions in the WHERE clause (e.g., WHERE YEAR(created_at) = 2024)
• Low cardinality (low value distribution), where a full scan is considered faster
• The column order does not match a composite index definition

To confirm whether an index is being used correctly, always check the key column in EXPLAIN.

7. Summary: Use EXPLAIN to Discover SQL Optimization Opportunities

Performance tuning in MySQL is not just about adding indexes.
The essential tool for identifying which queries are bottlenecks, why they are slow, and how to fix them is EXPLAIN.

In this article, we covered the following key points:

✅ The Role and Basic Usage of EXPLAIN

• Simply add EXPLAIN before a query to check its execution plan
• Issues such as full scans (ALL) and Using temporary become visible

✅ How to Read Output Columns and Evaluate Performance

• The four most important columns are type, key, rows, and Extra
• Avoid full table scans and aim for proper index utilization
• Be cautious when you see Using temporary or Using filesort

✅ Practical Diagnosis and Optimization Through Real Examples

• Not only adding indexes, but also improving SQL syntax matters
• Even complex queries with JOINs or subqueries can be analyzed using EXPLAIN
• Continuously refining queries based on execution plans is the fastest way to improve performance

✅ Use GUI Tools for Visual Confirmation

• Use “Visual EXPLAIN” in MySQL Workbench to view execution plans graphically
• Easier for beginners to identify bottlenecks visually
• Helpful for team discussions and SQL performance reviews

✅ FAQ Coverage for Real-World Scenarios

• Explained causes and solutions for issues like type=ALL and key=NULL
• Provided examples of why indexes may not be used

✍️ Make EXPLAIN a Habit to Improve Your SQL Skills

If you build a habit of checking queries with EXPLAIN every time you write SQL, you will naturally start writing faster and more efficient queries.

This is not just a technical trick — it is part of developing professional SQL literacy.

• Run EXPLAIN immediately after writing a query
• Fix suspicious execution plans right away
• Design efficient indexes thoughtfully

By mastering this cycle, your MySQL skills will steadily improve.

We hope this article becomes your first step toward better query optimization.

If you have questions or would like additional topics covered, feel free to leave a comment!