What is EXPLAIN? A command that visualizes execution plans
In MySQL, EXPLAIN (pronounced “explain”) is a command that visualizes how an SQL query is executed. It is especially used to understand how data is retrieved in SELECT statements, and it serves to display the query’s execution plan. For example, when you run a query such as SELECT * FROM users WHERE age > 30 against a table, you can learn through EXPLAIN which indexes MySQL is using and in what order it scans the tables. Usage is simple: just prepend EXPLAIN to the query.
EXPLAIN SELECT * FROM users WHERE age > 30;
When written this way, multiple columns describing the query’s execution plan are displayed. Each item will be explained in detail in later sections.
Don’t miss out! The reasons for slow queries become visible
Many developers fall into the trap of thinking, “The SQL works, so there’s no problem.” However, in reality, a slow query execution speed can negatively affect the overall performance of an application. Especially in services handling large amounts of data, a single inefficient query can become a bottleneck and place a heavy load on the server. EXPLAIN is useful in such cases. By checking the execution plan, you can visualize information such as which tables are being fully scanned (full table scans) and whether indexes are being used appropriately. In other words, using EXPLAIN allows you to identify the cause of poor performance and determine how to improve it. In particular, the effectiveness of indexes becomes clear when you look at EXPLAIN.
List of statements supported by EXPLAIN (SELECT/UPDATE, etc.)
EXPLAIN works not only with SELECT statements but also with other SQL statements such as:
SELECT
DELETE
INSERT
REPLACE
UPDATE
For example, when executing a DELETE statement on a large dataset, if indexes are not used, a full-table delete operation will be performed, and the processing time can increase dramatically. To prevent such situations, checking the execution plan with EXPLAIN before DELETE or UPDATE is extremely effective. Note that, depending on the MySQL version, a command called EXPLAIN ANALYZE is available to obtain even more detailed execution information. This will be covered in the later sections.
2. Understanding the EXPLAIN Output Columns (with illustrated images)
List and Explanation of Basic Output Columns
The EXPLAIN output displays columns like the following (they may vary slightly depending on the MySQL version).
Column Name
Description
id
Identifier indicating the execution order and grouping within the query.
select_type
Type of SELECT statement, such as subquery or UNION.
table
Name of the table being accessed.
type
Join type (access method) for the table.
possible_keys
Possible indexes that could be used.
key
Index actually used.
key_len
Length of the index used (in bytes).
ref
Value compared to the index.
rows
Number of rows MySQL estimates it will examine.
Extra
Additional information (e.g., sorting, temporary table usage).
Among these, the four items type / key / rows / Extra are especially important from a performance optimization perspective.
How to Interpret the Four Particularly Important Items “type / key / rows / Extra”
1. type (Access Method) This field indicates how the query accesses the table. It directly impacts performance.
Example Value
Meaning
Performance Indicator
ALL
Full table scan
✕ Slow
index
Full index scan
△
range
Range scan
○
ref / eq_ref
Reference using an index
◎
const / system
Limited to a single row
◎ (very fast)
type = ALL is the slowest access method, where no index is used and all rows are scanned. Ideally, you should improve the query to aim for ref or const. 2. key (Used Index) This field shows the name of the index that was actually used. If nothing is displayed, it likely means no index was applied. 3. rows (Estimated Rows Examined) This is the number of rows MySQL predicts it will scan during query execution. The larger this number, the longer the processing time tends to be. The goal is to optimize the query to bring rows = 1 as close as possible. 4. Extra (Additional Information) Extra displays additional information about query execution, such as sorting or temporary table usage. Items to watch out for are listed below.
Example Extra
Meaning
Suggested Action
Using temporary
Uses a temporary table (performance degradation)
Review GROUP BY and ORDER BY
Using filesort
Sorting is performed manually
Introduce index-based sorting
Using index
Data retrieved using only the index (fast)
○ Good state
If Using temporary or Using filesort appear, you need to review the SQL statements and index design.
Illustrated Example of EXPLAIN Result (Sample)
EXPLAIN SELECT * FROM users WHERE age > 30;
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
users
ALL
age_index
NULL
NULL
NULL
5000
Using where
In this example, although the index (age_index) exists, it is not used, resulting in ALL (full scan). There is room for improvement.
3. Learning by Example! How to Use and Interpret EXPLAIN
Example 1: EXPLAIN Result and Explanation for a Simple SELECT Query
First, let’s look at a simple SELECT query on a single table as an example.
EXPLAIN SELECT * FROM users WHERE age > 30;
Assume the EXPLAIN result for this query looks like the following.
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
users
ALL
age
NULL
NULL
NULL
5000
Using where
Explanation:
type: ALL → Full table scan. No index is being used.
key: NULL → No index is being used.
rows: 5000 → Approximately 5,000 rows are expected to be scanned.
Improvement: Adding an index to the age column can significantly improve query performance.
CREATE INDEX idx_age ON users(age);
Running EXPLAIN again will show that type becomes range or ref, confirming that the index is being used.
Example 2: Analyzing the Output of 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;
Result example:
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
users
ALL
PRIMARY, age
NULL
NULL
NULL
3000
Using where
1
SIMPLE
orders
ref
user_id
user_id
4
users.id
5
Using index
Explanation:
users table is being fully scanned (ALL), so this should be optimized.
On the other hand, the orders table uses an index with ref, which is efficient.
Improvement points:
Adding an index on users.age can speed up the scan of the users table.
Before joining, the key is to design indexes so that the WHERE clause can filter rows effectively.
Examples of Ineffective Indexes and How to Fix Them [Bad Example → Good Example]
Bad Example: WHERE Clause Using a Function
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';
In such a query, the index becomes ineffective because the DATE() function transforms the column, preventing MySQL from using the index. 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 makes the index on the created_at column usable, allowing efficient data retrieval.
Conclusion: Use Real EXPLAIN Examples to Diagnose Performance
By actually using EXPLAIN to analyze its output, you can see where the query bottlenecks are and how to optimize them.
ALL → Full scan! Consider adding an index.
key = NULL → No index used. Needs checking.
Extra contains Using temporary → Performance warning.
Using functions or expressions in conditions disables indexes.
Keeping these points in mind while using EXPLAIN enables routine query improvements.
4. Practical Query Optimization Techniques Using EXPLAIN Results
Fundamentals of Index Design to Avoid type: ALL
EXPLAIN shows type: ALL, it means a full table scan is being performed. This is a very costly operation and becomes a major bottleneck on tables with thousands to millions of rows. Measures to avoid:
Create indexes on columns used in WHERE clauses
CREATE INDEX idx_age ON users(age);
Consider composite indexes when multiple conditions exist
CREATE INDEX idx_status_created ON orders(status, created_at);
Avoid LIKE patterns that are not left-anchored
-- Bad example (index not used)
WHERE name LIKE '%tanaka%'
-- Good example (index may be used)
WHERE name LIKE 'tanaka%'
Meaning and Handling of “Extra: Using temporary”
When the Extra column shows “Using temporary”, it means MySQL is creating an internal temporary table to process the query. This usually occurs when operations such as GROUP BY or ORDER BY cannot be handled solely by indexes, and the server is using a temporary space to manually sort the data. Remedies:
Apply indexes to columns used in GROUP BY and ORDER BY
CREATE INDEX idx_group_col ON sales(department_id);
Eliminate unnecessary sorting and GROUP BY from the SQL
Use LIMIT and subqueries to narrow the target data
Understanding the Improvement Points Indicated by “rows” and “key”
The rows column shows how many rows MySQL expects to read from the table. For example, a query that reports rows = 100000 can have a significant impact on performance. When this value is large, you need to apply indexes to reduce the number of rows scanned and review the predicate conditions. Conversely, the key column shows the index actually used. If it is NULL, it warns that no index is being used. Improvement checkpoints:
rows high → Are the filter conditions appropriate? Is the index being used?
key = NULL → Are you using syntax in WHERE or JOIN that prevents index usage?
Make the EXPLAIN and Optimization Cycle a Habit
To tune queries, the basic practice is to repeat the cycle of write → verify with EXPLAIN → improve → re-verify. Keep the following flow in mind.
First, write the query as usual
Check the execution plan with EXPLAIN
Check type, key, rows, and Extra
If there are bottlenecks, review indexes and syntax
Confirm the improvements again with EXPLAIN
Query performance is influenced not only by the presence of indexes but also by how the query itself is written. Techniques such as avoiding functions and using simple comparison operators can also be effective.
5. Visual Analysis Using MySQL Workbench’s Visual EXPLAIN
Check execution plans visually with a GUI tool
MySQL Workbench is a GUI tool specialized for MySQL administration and development. Even execution plans that are hard to read in terminal output are a major feature that visualizes them diagrammatically.
Using Visual EXPLAIN, you can view the following information in a tree structure:
Access order of each table
Types of JOIN
Index usage status
Presence of full table scans
Presence of data filtering or sorting operations
Because it is displayed as a diagram, even beginners can easily grasp visually “where the bottlenecks are”.
Visual Explain: How to view and use it (with images)
The steps to use Visual EXPLAIN are as follows:
Launch MySQL Workbench and open the target database → Make sure the connection is set up in advance.
Enter the target query in the SQL editor
SELECT * FROM users WHERE age > 30;
Click the “EXPLAIN VISUAL” icon to the right of the “EXPLAIN” button → Or select “Visual Explain” from the right-click menu.
The execution plan is displayed visually Clicking each node (table) shows detailed information such as:
Access method (ALL, ref, range, etc.)
Index used
Estimated row count (rows)
Filter conditions and JOIN type
Note: In Visual Explain, the color and icons of each node make heavy or inefficient operations immediately apparent.
Especially, “nodes displayed in red” should be checked as performance warnings.
Even beginners can easily spot bottlenecks!
Many find text‑based EXPLAIN hard to read until they get used to it, but with Visual Explain, the “problem areas” are highlighted visually.
For example, it becomes easier to notice cases such as:
Tables where type: ALL is used
Query blocks where Using temporary occurs
Patterns with many unnecessary JOINs
Identifying tables that are not using indexes
The GUI allows you to quickly formulate hypotheses about tuning points, and it is convenient for sharing and reviewing within a team.
Visual EXPLAIN is an especially reassuring tool for SQL beginners to intermediate users.
If you’re unsure how to interpret EXPLAIN results, be sure to take advantage of this feature.
6. Frequently Asked Questions (FAQ)
Q1. When should EXPLAIN be used?
A. Use it when you are concerned about query execution speed, especially when you have SQL that feels “somewhat slow.” It’s also useful when you want to verify that a newly created query is using indexes appropriately. By using EXPLAIN before execution to review the execution plan, you can identify performance risks before release.
Q2. The output “type” is ALL—what should I do?
A.type: ALL indicates that MySQL is performing a full table scan. This is a very costly operation and can significantly degrade performance, especially on tables with many rows. As a remedy, consider the following:
Add an index to the column used in the WHERE clause
Avoid functions or expressions that prevent the index from being used
Avoid SELECT * and retrieve only the columns you need
Q3. The Extra column shows “Using temporary”—is that a problem?
A.Using temporary means MySQL is creating an internal temporary table to process the query. It often occurs because of GROUP BY or ORDER BY, and incurs memory and disk I/O costs. As a mitigation:
Add indexes on columns used in GROUP BY / ORDER BY
Avoid unnecessary sorting or aggregation, and consider handling it in the application layer
Use LIMIT or subqueries to reduce the amount of data processed
Q4. How do I use Visual EXPLAIN?
A. By using the official MySQL tool “MySQL Workbench,” you can visualize EXPLAIN results easily on a GUI. It’s straightforward: after entering your query, just click the “Visual Explain” button. It’s especially recommended for:
People who find text‑based EXPLAIN output hard to read
Those who want to visually grasp the structure of JOINs or complex queries
When you want to share and discuss SQL performance with a team
Q5. Why isn’t an existing index being used?
A. Even if an index exists, MySQL may not use it. Indexes are ignored in cases such as:
Using functions or operators in the WHERE clause (e.g., WHERE YEAR(created_at) = 2024)
When cardinality (value distribution) is low and a full scan is judged faster
When the order of columns in the query doesn’t match the order of a composite index
To verify that an index is being used correctly, always check the key column in EXPLAIN.
7. Summary | Using EXPLAIN reveals SQL improvement points!
Performance tuning in MySQL is not enough by simply adding indexes. Identifying which queries are bottlenecks, why they are slow, and uncovering the causes is essential, and the indispensable tool for that is “EXPLAIN”. In this article, we have explained the following key points.
✅ Role and basic usage of EXPLAIN
Just prepend EXPLAIN to a query to view its execution plan.
Issues like full scans (ALL) and Using temporary become visible.
✅ How to read output fields and assess performance
type, key, rows, and Extra are especially important.
Avoid full scans; an ideal state is when indexes are being used.
Watch out for Using temporary or Using filesort in the output.
✅ Practical diagnosis and optimization methods with real examples
Adding indexes is important, but so is crafting the SQL syntax.
Even complex SQL with JOINs and subqueries can be broken down with EXPLAIN.
Analyzing the execution plan while refining the query is the shortcut to better performance.
✅ How to use GUI tools for visual verification
Use MySQL Workbench’s “Visual EXPLAIN” to view the execution plan as a diagram.
Beginners can more easily spot bottlenecks visually.
Convenient for sharing SQL performance insights with the team.
✅ FAQ addresses real‑world concerns
Summarize causes and solutions for issues like type=ALL or key=NULL.
Explain why indexes may not be used, with examples.
✍️ Making EXPLAIN a habit dramatically improves your SQL skills
If you get into the habit of checking each query with EXPLAIN, you’ll unconsciously start writing “fast queries”. This is not just a technique; it’s the foundation of “SQL literacy” as a professional.
Run EXPLAIN immediately after writing a query
Fix any odd execution plans right away
Consider efficient index design
By adopting this cycle, your MySQL skills will reliably level up. I hope this article serves as the first step in your query optimization journey.
If you have any questions or topics you’d like expanded, feel free to let me know in the comments!
