Detailed explanation of MySQL EXPLAIN output columns

1. Introduction

The EXPLAIN statement provides information about how MySQL executes a statement.

EXPLAIN is used with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10001;
+----+-------------+-----------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
| 1 | SIMPLE | employees | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

Simply put, EXPLAIN can be used to analyze whether the SQL statement uses the index and what index it uses.

EXPLAIN returns one row for each table used in the SELECT statement. It lists the tables in the output in the order in which MySQL reads them while processing the statement.

MySQL resolves all joins using Nested-Loop Join Algorithms, which means that MySQL reads a row from the first table, then finds a matching row in the second table, the third table, and so on. After processing all tables, MySQL backtracks through the table list after outputting the selected columns until it finds a table in which more matching rows exist. Read the next row from that table and continue with the next table.

2. EXPLAIN Output Columns

MySQL version 5.7.33
Windows 10 64-bit

From the figure above, we can see the results of EXPLAIN, including the table header id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, and Extra. Let's learn the meaning of these fields and understand them through examples.

2.1 id

SELECT identifier, the sequence number of the SELECT in the query. This value can be NULL if the row refers to the union result of other rows. In this case, the table column displays a value like <unionM,N> to indicate that the row refers to the union of the rows with id values M and N.

There are three types of id values:

The id is the same, the execution order is from top to bottom

mysql> EXPLAIN (
    -> SELECT * FROM employees emp
    -> LEFT JOIN dept_emp de ON emp.emp_no = de.emp_no
    -> LEFT JOIN departments dept ON dept.dept_no = de.dept_no
    -> WHERE emp.emp_no = 10001);
+----+-------------+-------+------------+--------+---------------+--------+---------+----------------------+------+----------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+--------+---------+----------------------+------+----------+------+
| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | de | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | dept | NULL | eq_ref | PRIMARY | PRIMARY | 12 | employees.de.dept_no | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+--------+---------+----------------------+------+----------+------+
3 rows in set, 1 warning (0.03 sec)

The ids are different. If it is a subquery, the id number will increase. The larger the id value, the higher the priority of execution.

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no NOT IN ( SELECT de.emp_no FROM dept_emp de 
    -> WHERE de.dept_no NOT IN ( SELECT dept_no FROM departments WHERE dept_name = 'Development'));
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 100.00 | Using where |
| 2 | SUBQUERY | de | NULL | index | PRIMARY | dept_no | 12 | NULL | 308493 | 100.00 | Using where; Using index |
| 3 | SUBQUERY | departments | NULL | const | PRIMARY,dept_name | dept_name | 122 | const | 1 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

Both the same and different ids exist

If the id is the same, they can be considered as a group. The execution order of the same group id is from top to bottom. Among different groups, the larger the id value, the higher the execution priority.

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN ( SELECT de.emp_no FROM dept_emp de 
    -> WHERE de.dept_no IN ( SELECT dept_no FROM departments WHERE dept_name LIKE '%Develop%'));
+----+--------------+-------------+------------+-------+-----------------+-----------+--------+-------------------------------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+-------+-----------------+-----------+--------+-------------------------------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
| 1 | SIMPLE | emp | NULL | ALL | PRIMARY | NULL | NULL | NULL | 299468 | 0.00 | Using where; Using join buffer (Block Nested Loop) |
| 2 | MATERIALIZED | departments | NULL | index | PRIMARY | dept_name | 122 | NULL | 9 | 11.11 | Using where; Using index |
| 2 | MATERIALIZED | de | NULL | ref | PRIMARY,dept_no | dept_no | 12 | employees.departments.dept_no | 38561 | 100.00 | Using index |
+----+--------------+-------------+------------+-------+-----------------+-----------+--------+-------------------------------+--------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.01 sec)

2.2 select_type

The query type is mainly used to distinguish common queries, joint queries, subqueries and other complex queries.

Includes SIMPLE, PRIMARY, UNION, DEPENDENT UNION, UNION RESULT, SUBQUERY, DEPENDENT SUBQUERY, DERIVED, MATERIALIZED, UNCACHEABLE SUBQUERY, UNCACHEABLE UNION

SIMPLE

Simple SELECT, no UNION or subqueries.

mysql> EXPLAIN select * from employees where emp_no=10001;
+----+-------------+-----------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
| 1 | SIMPLE | employees | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

PRIMARY

If the query contains any complex sub-parts, the outermost query is marked as PRIMARY

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN ( SELECT max(emp_no) FROM dept_emp);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

UNION

The second or subsequent SELECT statements that appear after a UNION are marked as UNION

mysql> EXPLAIN (SELECT emp_no,dept_no FROM dept_emp LIMIT 10)
    -> UNION
    -> SELECT emp_no,dept_no FROM dept_manager;
+----+--------------+--------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+--------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
| 1 | PRIMARY | dept_emp | NULL | index | NULL | dept_no | 12 | NULL | 308493 | 100.00 | Using index |
| 2 | UNION | dept_manager | NULL | index | NULL | dept_no | 12 | NULL | 24 | 100.00 | Using index |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+--------------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

DEPENDENT UNION

Same as UNION, it appears in a UNION or UNION ALL statement, but this query is affected by the outer query

| UNION RESULT union_result Result of a UNION.
| SUBQUERY None First SELECT in subquery
| DEPENDENT SUBQUERY dependent (true) First SELECT in subquery, dependent on outer query
| DERIVED None Derived table
| MATERIALIZED materialized_from_subquery Materialized subquery
| UNCACHEABLE SUBQUERY cacheable (false) A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
| UNCACHEABLE UNION cacheable (false) The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

Summarize

This is the end of this article about MySQL EXPLAIN output columns. For more information about MySQL EXPLAIN output columns, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in: