Course: MySQL Training
Topic: Query Analysis Using EXPLAIN
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
Query analysis is the process of examining how MySQL executes an SQL query and identifying opportunities to improve its performance. As databases grow, queries that once ran quickly may become slow because of large tables, missing indexes, inefficient joins, unnecessary sorting, or poorly written conditions.
MySQL provides the EXPLAIN statement to show the execution plan for a query. The execution plan describes how MySQL intends to retrieve data, which tables it will access, which indexes it may use, how rows are accessed, and the order in which tables are processed.
Understanding EXPLAIN helps developers and database administrators diagnose performance problems before changing queries or database structures.
After completing this topic, learners should be able to:
EXPLAIN.EXPLAIN is a MySQL statement that displays information about the execution plan for a supported SQL statement. It helps you understand how the optimizer plans to retrieve or modify data.
For a SELECT query, the execution plan can reveal the access method, candidate indexes, selected index, estimated number of rows examined, filtering estimates, and other details.
EXPLAIN
SELECT column1, column2
FROM table_name
WHERE condition;EXPLAIN
SELECT *
FROM employees
WHERE department_id = 10;This statement asks MySQL to display the execution plan for the query. It does not normally execute the SELECT query to retrieve its result set; instead, it provides information about the planned execution.
The plan is an optimizer estimate, not a guarantee of the exact work that will occur at runtime. Actual behavior can vary with table statistics, data distribution, indexes, server configuration, and parameter values.
Consider the following example table for employee records.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department_id INT,
salary DECIMAL(10,2),
city VARCHAR(50)
);Insert a few sample records:
INSERT INTO employees
(employee_id, employee_name, department_id, salary, city)
VALUES
(1, 'Aarav', 10, 45000, 'Mumbai'),
(2, 'Diya', 20, 55000, 'Pune'),
(3, 'Kabir', 10, 60000, 'Mumbai'),
(4, 'Meera', 30, 48000, 'Delhi'),
(5, 'Rohan', 20, 70000, 'Pune');Now analyze this query:
EXPLAIN
SELECT employee_name, salary
FROM employees
WHERE department_id = 10;MySQL evaluates possible access methods and chooses a plan based on its estimates and available indexes. The exact output depends on the MySQL version, table statistics, and indexes defined on the table.
In the traditional tabular format, EXPLAIN commonly returns columns such as the following. Some columns or values may vary by MySQL version and query type.
| Column | Meaning |
|---|---|
id |
Identifies a SELECT query block within the execution plan. |
select_type |
Describes the type of SELECT block, such as SIMPLE or PRIMARY. |
table |
Indicates the table or derived result being accessed. |
partitions |
Shows the partitions considered for the query, when applicable. |
type |
Describes the access method MySQL uses for the table. |
possible_keys |
Lists indexes that the optimizer considers potentially useful. |
key |
Shows the index selected by the optimizer, if any. |
key_len |
Shows the length of the selected index portion used by the plan. |
ref |
Shows columns or constants compared with the selected index. |
rows |
Estimates how many rows MySQL may need to examine for that table access. |
filtered |
Estimates the percentage of examined rows expected to pass a condition. |
Extra |
Provides additional execution-plan details. |
The type column describes how MySQL accesses a table. It is an important clue when reviewing a plan, but it must be interpreted in context. A full table scan can be reasonable for a small table or a query that needs most of its rows.
| Access Type | Description | General Interpretation |
|---|---|---|
system |
A special case of a table with one row. | Usually very inexpensive. |
const |
A table is accessed using a unique index lookup that can return at most one matching row. | Usually efficient. |
eq_ref |
A unique index lookup is performed for each combination of rows from preceding tables. | Often efficient for joins. |
ref |
An index lookup returns rows matching a value that is not necessarily unique. | Often useful. |
range |
An index is used to retrieve a range of values. | Often useful for ranges and inequalities. |
index |
MySQL scans an index rather than performing a full table scan. | Can still be expensive if many index entries are scanned. |
ALL |
MySQL performs a full table scan. | Investigate for large tables, but do not assume it is always wrong. |
EXPLAIN
SELECT *
FROM employees
WHERE city = 'Mumbai';If no useful index exists on city, MySQL may choose ALL and inspect the table's rows. Whether this is a problem depends on the table size, selectivity of the condition, and the query's overall cost.
The possible_keys column lists indexes that might help the query. The key column identifies the index that MySQL actually selects for that plan.
CREATE INDEX idx_employees_department
ON employees(department_id);Then examine the query again:
EXPLAIN
SELECT employee_name, salary
FROM employees
WHERE department_id = 10;MySQL may choose the new index if its optimizer estimates that using it is less expensive. On a tiny sample table, it may still choose a scan because scanning a few rows can be cheaper than accessing an index and then retrieving table records.
The rows column is an estimate of the number of rows MySQL expects to examine for the table access. It is not necessarily the number of rows returned to the user.
The filtered column estimates the percentage of examined rows that remain after additional filtering conditions are applied. It is an estimate, not a guaranteed measured percentage.
For example, if EXPLAIN estimates that 1,000 rows will be examined and 10% will pass a filter, the plan may estimate that approximately 100 rows remain after that filtering stage. This is only an illustration of how the estimates relate.
The Extra column contains additional information about the planned execution. The following are common values or descriptions you may encounter.
| Extra Value | Meaning | What to Check |
|---|---|---|
Using where |
A condition is used to filter rows. | Check whether the condition is selective and whether a useful index can help. |
Using index |
The query can be satisfied from index entries without needing to read the full table rows for the selected columns. | This can be efficient; confirm that the index supports the query well. |
Using filesort |
MySQL performs an additional sorting operation rather than satisfying the order directly through the chosen index. | Review ORDER BY columns, indexes, and the number of rows being sorted. |
Using temporary |
A temporary table is used for part of query processing. | Review GROUP BY, DISTINCT, ORDER BY, and the query structure. |
Using index condition |
Index Condition Pushdown is used to evaluate a condition using index entries before fetching full rows where possible. | Check whether the index and predicate are appropriate. |
Impossible WHERE |
The optimizer determines that the WHERE condition cannot be true. | Check for contradictory conditions or incorrect values. |
These messages are diagnostic clues, not automatic proof of a performance problem. For example, Using filesort does not necessarily mean that MySQL writes all sorted data to disk, and Using temporary is not always avoidable.
The WHERE clause filters rows. EXPLAIN helps determine whether MySQL can use an index to find matching records efficiently.
EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 20;If an appropriate index exists, MySQL may use it to locate matching employees. The chosen access method depends on the index structure, table statistics, and estimated number of matching records.
EXPLAIN
SELECT *
FROM employees
WHERE YEAR(hire_date) = 2025;Applying a function to a column can prevent a conventional index on that column from being used for a direct range lookup. If the goal is to retrieve records within a year, a date range is often more index-friendly.
SELECT *
FROM employees
WHERE hire_date >= '2025-01-01'
AND hire_date < '2026-01-01';Use this example only when the table has a compatible hire_date column. A suitable index on that column may support a range access.
Joins combine related rows from two or more tables. EXPLAIN helps identify join order, access methods, selected indexes, and estimated row counts for each table.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);EXPLAIN
SELECT e.employee_name, d.department_name
FROM employees AS e
JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_id = 10;When analyzing the plan:
type and key columns for each table.Sorting and grouping can require additional processing, particularly when large result sets are involved.
EXPLAIN
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC;If the query must sort many rows, EXPLAIN may show Using filesort. An index that matches the filtering and ordering requirements may help in some cases, but its usefulness depends on the query and index structure.
EXPLAIN
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;Check for temporary-table or sorting indicators and review how much data is being processed. Do not add indexes without checking the actual query pattern and the trade-offs for inserts, updates, storage, and maintenance.
MySQL supports multiple execution-plan formats. The JSON format presents plan information as a structured document, which is useful for investigating more detailed optimizer information.
EXPLAIN FORMAT=JSON
SELECT employee_name, salary
FROM employees
WHERE department_id = 10;JSON output may contain details such as table names, access types, selected indexes, estimated rows, filtering estimates, attached conditions, and cost information.
The TREE format represents the execution plan as a hierarchy of operations. It can make the order of operations easier to understand for complex queries. It also displays details such as hash joins that are not shown in the traditional format.
EXPLAIN FORMAT=TREE
SELECT employee_name, salary
FROM employees
WHERE department_id = 10;Use TREE when you want a more descriptive view of the operations MySQL expects to perform. Availability and supported features depend on your MySQL version.
EXPLAIN ANALYZE executes a supported statement and reports the execution plan along with runtime measurements. It helps compare the optimizer's estimates with observed execution behavior.
EXPLAIN ANALYZE
SELECT employee_name, salary
FROM employees
WHERE department_id = 10;In supported MySQL versions, EXPLAIN ANALYZE uses TREE output and reports actual execution details for plan operations.
| Information | Meaning |
|---|---|
| Estimated cost | The optimizer's estimated cost for an operation. |
| Estimated rows | The expected number of rows produced by an operation. |
| Actual time | Measured time associated with execution of the operation. |
| Actual rows | The number of rows actually produced by the operation. |
| Loops | The number of times the operation ran. |
Important: Unlike a regular EXPLAIN of a SELECT query, EXPLAIN ANALYZE actually runs the query. Use it thoughtfully with expensive queries or production workloads, because it can consume resources and take time.
One of the most useful applications of EXPLAIN ANALYZE is to identify where optimizer estimates differ from observed results.
For example, MySQL might estimate that an operation returns 20 rows but the operation actually returns 5,000 rows. A large difference can affect join choices and the overall execution strategy.
Large estimate differences are clues for further investigation, not proof that the optimizer is incorrect. Always evaluate them alongside the query, schema, data distribution, and observed performance.
A consistent workflow makes query tuning easier to measure and repeat.
type, possible_keys, and key columns.| Problem | Possible Cause | Recommended Investigation |
|---|---|---|
| Slow query on a large table | Too many rows are scanned. | Inspect the access method, filters, and available indexes. |
| Index is not selected | The table is small, many rows match, or the index is not useful for the predicate. | Review the plan, query selectivity, and table statistics before changing indexes. |
| Slow JOIN | Join conditions or indexes are inefficient. | Check join columns, data types, row estimates, and the chosen access method. |
| Slow ORDER BY | Many rows require sorting. | Review the filter and ordering columns; test whether an appropriate index helps. |
| Unexpected row estimates | Statistics may be stale or the data distribution may be uneven. | Consider updating table statistics and comparing estimated versus actual execution. |
| Large temporary operation | Grouping, sorting, or other processing requires intermediate results. | Review the query shape and whether the intermediate work is necessary. |
| Query returns too much data | Unnecessary columns or rows are requested. | Select only required columns and apply appropriate filtering. |
MySQL's optimizer relies on table and index statistics to estimate query costs. When statistics do not represent the current data well, the optimizer may choose a less suitable plan.
The ANALYZE TABLE statement updates key distribution statistics for supported storage engines.
ANALYZE TABLE employees;After updating statistics, inspect the plan again:
EXPLAIN
SELECT employee_name, salary
FROM employees
WHERE department_id = 10;Running ANALYZE TABLE does not guarantee that a query will become faster. Confirm the result using the execution plan and representative performance tests.
ALL access type as a problem; consider table size and query requirements.key = NULL.EXPLAIN
SELECT *
FROM employees
WHERE department_id = 20;Tasks:
EXPLAIN
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC;Tasks:
EXPLAIN
SELECT e.employee_name, d.department_name
FROM employees AS e
JOIN departments AS d
ON e.department_id = d.department_id;Tasks:
EXPLAIN ANALYZE
SELECT employee_name
FROM employees
WHERE department_id = 10;Tasks:
Project Goal: Practice analyzing query plans and validating improvements using a sample employee database.
Answer: EXPLAIN displays optimizer information about how MySQL plans to execute a supported SQL statement, including table access methods, indexes, row estimates, and other plan details.
Answer: A regular EXPLAIN of a SELECT normally displays its execution plan without retrieving the SELECT result set. EXPLAIN ANALYZE, by contrast, executes the supported query to collect runtime information.
Answer: It indicates a full table scan for that table access. This can be expensive on a large table, but it can be reasonable when a table is small or most rows are needed.
Answer: possible_keys lists candidate indexes the optimizer considers, while key identifies the index selected for the plan, if one is used.
Answer: It estimates the number of rows MySQL expects to examine for the relevant table access. It is not necessarily the number of rows returned to the user.
Answer: It indicates that MySQL performs an additional sorting operation instead of obtaining the required ordering directly from the chosen index. It does not automatically mean disk sorting or a serious performance problem.
Answer: It presents execution-plan information as structured JSON, which can expose detailed optimizer estimates and access information.
Answer: It executes a supported query and provides runtime measurements alongside the execution plan, allowing estimated behavior to be compared with actual execution.
Answer: The optimizer may estimate that a table scan is cheaper, the condition may match many rows, the index may not support the query effectively, or statistics may influence the decision.
Answer: Compare estimated and actual row counts where available, examine data distribution, check table statistics, and use ANALYZE TABLE when appropriate. Then test whether the plan and performance improve.
Answer: No. Indexes consume storage and add work to inserts, updates, and deletes. They should be designed for actual query patterns and validated through testing.
Answer: The goal is to understand where database work occurs, identify plausible performance bottlenecks, and validate changes that improve performance without creating unacceptable trade-offs.
id, select_type, table, and type.possible_keys, key, key_len, and ref.rows and filtered as estimates.Using where, Using index, Using filesort, and Using temporary.Query Analysis Using EXPLAIN is an essential MySQL skill for understanding how SQL statements access tables, use indexes, process joins, filter records, and perform sorting or grouping. By interpreting execution-plan information, developers can identify areas that deserve further investigation and make informed optimization decisions.
For the most reliable results, use EXPLAIN to inspect the plan, EXPLAIN ANALYZE when actual runtime information is needed and safe to collect, and repeatable performance tests to confirm whether a change truly helps. Effective query optimization is based on evidence, realistic data, and a clear understanding of trade-offs.
