Overview of MySQL Execution Plan Fields

Overview of MySQL Execution Plan Fields

id

For each SELECT keyword in a query, MySQL assigns a unique id value. Exceptions exist, such as when the optimizer transforms a subquery into a semi‑join, where two queries may share the same id.

mysql> explain select * from t1 where a in (select b from t2 where t2.b=100);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                                              |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | a             | a    | 5       | const |    1 |   100.00 | NULL                                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL  |    1 |   100.00 | Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------------+

Another special case is when id is NULL, for example:

mysql> explain select * from t1 union select * from t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
|NULL| UNION RESULT |
| NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |   NULL   | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

The NULL id occurs because UNION results are de‑duplicated using a temporary table named , so the combined query receives no single id.

select_type

Indicates the type of SELECT operation.

1. SIMPLE

A query without UNION or subqueries. Example of a single‑table SIMPLE select:

mysql> explain select * from t1 where b=1 order by a;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_b         | idx_b | 5       | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+

Join queries are also SIMPLE:

mysql> explain select * from t1 join t2 on t1.a=t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | a             | NULL | NULL    | NULL      |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | t1    | NULL       | ref  | a             | a    | 5       | hucq.t2.a |    1 |    5.00  | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+

2. PRIMARY

For queries that consist of multiple SELECTs (e.g., UNION or subqueries), the left‑most SELECT gets the PRIMARY type.

mysql> explain select * from t1 union select * from t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
|NULL| UNION RESULT |
| NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |   NULL   | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

3. UNION

All SELECTs after the first one in a UNION (or UNION ALL) are marked UNION.

4. UNION RESULT

The temporary table created to hold the UNION result is accessed with the UNION RESULT type.

5. SUBQUERY

When a subquery cannot be turned into a semi‑join but can be materialized, it appears as SUBQUERY, meaning the subquery is executed once and its result is stored in a temporary table.

mysql> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
+----+-------------+-------+-------+---------+------+------+-------------+
| id | select_type | table | type  | key   | ref  | rows | Extra       |
+----+-------------+-------+-------+---------+------+------+-------------+
|  1 | PRIMARY     | t1    | ALL   | NULL  | NULL |  100 | Using where |
|  2 | SUBQUERY    | t2    | range | PRIMARY| NULL |    9 | Using where |
+----+-------------+-------+-------+---------+------+------+-------------+

6. DEPENDENT SUBQUERY

If a subquery is correlated (or materialization is disabled) and cannot be turned into a semi‑join, the optimizer treats it as a DEPENDENT SUBQUERY, which is executed repeatedly for each row of the outer query.

mysql> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
+----+--------------------+-------+-------+---------+------+----------+-------------+
| id | select_type        | table | type  | key   | rows | filtered | Extra       |
+----+--------------------+-------+-------+---------+------+----------+-------------+
|  1 | PRIMARY            | t1    | ALL   | NULL  |  100 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | range | PRIMARY|   9 |    10.00 | Using where |
+----+--------------------+-------+-------+---------+------+----------+-------------+

7. MATERIALIZED

When a subquery is optimized as a semi‑join using the Materialize strategy, the subquery appears as MATERIALIZED; its result is materialized into a temporary table that is then joined.

mysql> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
+----+--------------+-------------+-------+---------+---------------+------+-------------+
| id | select_type  | table       | type  | key   | ref           | rows | Extra       |
+----+--------------+-------------+-------+---------+---------------+------+-------------+
|  1 | SIMPLE       |
| ALL   | NULL  | NULL          | NULL | Using where |
|  1 | SIMPLE       | t1          | ref   | a     |
.b |    1 | NULL        |
|  2 | MATERIALIZED | t2          | range | PRIMARY| NULL          |    9 | Using where |
+----+--------------+-------------+-------+---------+---------------+------+-------------+

8. DERIVED

If a derived subquery cannot be merged (derived_merge disabled), the optimizer materializes it, and the subquery receives the DERIVED type.

mysql> explain select * from t1 join (select distinct a from t2) as derived_t2 on t1.a=derived_t2.a;
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref            | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+
|  1 | PRIMARY     | t1         | NULL       | ALL   | a             | NULL        | NULL    | NULL           |  100 |   100.00 | Using where |
|  1 | PRIMARY     |
| NULL       | ref   |
|
| 5       | join_test.t1.a |   10 |   100.00 | Using index |
|  2 | DERIVED     | t2         | NULL       | index | a             | a           | 5       | NULL           | 1000 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+

For deeper discussion of subquery and derived‑table optimization strategies, see the related articles on MySQL subquery optimization and derived‑table optimization.

type

The access method column, ordered from best to worst: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL. Queries should aim for at least range, preferably ref.

1. system

If a table contains only one row and the storage engine provides exact statistics (e.g., MyISAM, Memory), the access method is system.

mysql>  explain select * from t_myisam;
+----+-------------+----------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_myisam | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+--------+---------------+------+---------+------+------+----------+-------+

InnoDB tables with a single row are reported as ALL.

mysql> explain select * from t5;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t5    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

2. const

When the query uses a primary key or a unique secondary index to match a constant value, the access method is const.

mysql> explain select * from t1 where id=100;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const| PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

3. eq_ref

In join queries, if the driven table is accessed via a primary key or a unique secondary index with an equality condition, the method is eq_ref.

mysql> explain select * from t1 join t2 on t1.id=t2.id where t1.a<50;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY,a    | PRIMARY | 4       | hucq.t2.id |    1 |    5.00  | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+

4. ref

When a non‑unique secondary index is used for an equality match, the method is ref, even if multiple rows match.

mysql> explain select * from t11 where a=100;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t11   | NULL       | ref  | a             | a    | 5       | const |  500 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

5. ref_or_null

If the indexed column can be NULL and the query includes a condition that matches NULL, the method may be ref_or_null.

mysql> explain select * from t11 where a=100 or a is null;
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t11   | NULL       | ref_or_null | a             | a    | 5       | const |  501 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+

6. index_merge

When the optimizer can use multiple indexes together (Intersection, Union, Sort‑Union), the method is index_merge.

mysql> explain select * from t1 where a<50 or b=50;
+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | a,idx_b       | a,idx_b | 5,5     | NULL |   50 |   100.00 | Using sort_union(a,idx_b); Using where |
+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+

7. unique_subquery

If an IN subquery cannot be turned into a semi‑join or materialized, but the subquery can use a primary or unique key for equality, the optimizer treats it as unique_subquery.

mysql> explain select * from t1 where a in (select id from t2 where t1.a=t2.a) or b=100;
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL             | idx_b         | NULL    | NULL    | NULL | 1000 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | unique_subquery | PRIMARY,a    | PRIMARY | 4       | func |    1 |   100.00 | Using where |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+

8. range

If an index is used to retrieve a range of rows, the method is range.

mysql> explain select * from t1 where a<50 and a>20;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |   29 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

9. index

When the optimizer scans the entire index (full index scan), the method is index, which is relatively costly.

mysql> EXPLAIN SELECT key_part1 FROM s1 WHERE key_part3 = 'a';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | s1    | NULL       | index | NULL          | idx_key_part | 909     | NULL | 9688 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

possible_keys and key

The possible_keys column lists indexes that could be used for a single‑table query, while the key column shows the index actually chosen.

key_len

Shows the length of the index key MySQL decided to use; a shorter length is preferable as long as it does not lose precision.

ref

When an index is used for an equality match, ref displays the constant or column used for the match; otherwise it is NULL.

rows

For a full‑table scan, rows is the estimated number of rows to read; for an index scan, it is the estimated number of index entries to read.

filtered

For a full‑table scan, filtered is the percentage of rows that satisfy the WHERE clause; for an index scan, it is the percentage of rows retrieved from the index that also satisfy other conditions.

Extra

The Extra column provides additional details about the query execution, such as temporary table usage, filesort, using where, etc.

Further discussion of some Extra states is covered in a separate article.