Detailed explanation of MySQL EXPLAIN output columns

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:
  • In-depth analysis of explain in MySQL query optimization
  • Detailed explanation of explain usage in MySQL
  • MySQL summary explain
  • MySQL performance analysis and explain usage instructions
  • Detailed explanation of the role of explain in MySQL
  • Detailed explanation of the use of mysql explain (analysis index)
  • Detailed explanation of the EXPLAIN command and its usage in MySQL
  • Detailed explanation of the execution plan explain command example in MySQL
  • MYSQL explain execution plan
  • Detailed explanation of EXPLAIN command in MySQL

<<:  I have sorted out some domestic design websites that I think are good.

>>:  Vue components dynamic components detailed explanation

Recommend

Docker container accesses the host's MySQL operation

background: There is a flask project that provide...

How to install Elasticsearch7.6 cluster in docker and set password

Table of contents Some basic configuration About ...

CSS tips for implementing Chrome tab bar

This time let’s look at a navigation bar layout w...

mysql having usage analysis

Usage of having The having clause allows us to fi...

Understanding and solutions of 1px line in mobile development

Reasons why the 1px line becomes thicker When wor...

Detailed explanation of the implementation principle of ACID transaction in Mysql

introduction As usual, let's start with a sce...

How to use Axios asynchronous request API in Vue

Table of contents Setting up a basic HTTP request...

CSS3 countdown effect

Achieve results Implementation Code html <div ...

Detailed explanation of the difference between arrow functions and normal functions in JavaScript

This article explains the difference between arro...

MySQL uses inet_aton and inet_ntoa to process IP address data

This article will introduce how to save IP addres...

Detailed explanation of Vue lazyload picture lazy loading example

Documentation: https://github.com/hilongjw/vue-la...

vitrualBox+ubuntu16.04 install python3.6 latest tutorial and detailed steps

Because I need to use Ubuntu+Python 3.6 version t...

Mysql stores tree structure through Adjacency List (adjacency list)

The following content introduces the process and ...

JavaScript Shorthand Tips

Table of contents 1. Merge arrays 2. Merge arrays...

CSS position fixed left and right double positioning implementation code

CSS Position The position attribute specifies the...