MySQL explain command explained

The Explian command can display the execution plan of the SELECT statement

Each row in the result of explain corresponds to a table in the SELECT statement, and the order in the output is the order in which the tables are processed by the statement. MySQL uses a nested loop to handle all join connections. When the keyword extended is used, explain can view the contents of the "show warnings" statement, as well as the filtered columns.

The keyword "extented" and "partitions" cannot be used together, and after 5.6.5, none of these two keywords can be used with "format".

Mysql> explain select * from EMP, dept where emp.deptno=dept.deptno;+----+-------------+-------+--------+---------- -----+------+---------+------+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |  Extra |+----+-------------+-------+--------+---------------+------+---------+------+------+-------+| 1 | Simple | EMP | System | NULL | NULL | NULL |    NULL |       1 |  || 1 | Simple | Dept | System | NULL | NULL | NULL |    NULL |       1 | |+----+-------------+-------+--------+---------------+------+---------+------+------+-------+2 rows in Set (0.00 SEC) mysql> Explain select * from dept,emp where emp.deptno=dept.deptno;+----+-------------+-------+--------+------ ---------+------+---------+------+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |  Extra |+----+-------------+-------+--------+---------------+------+---------+------+------+-------+| 1 |Simple | Dept | System | NULL | NULL | NULL |    NULL |       1 |  || 1 | Simple | EMP | System | NULL | NULL | NULL |    NULL |       1 | |+----+-------------+-------+--------+---------------+------+---------+------+------+-------+2 rows in Set (0.00 SEC) mysql> Explain select * FROM (SELECT * FROM (SELECT * from EMP where id=1) a) b;+----+-------------+------------+- -------+---------------+------+---------+------+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |  Extra |+----+-------------+------------+--------+---------------+------+---------+------+------+-------+| 1 | PRIMARY | <derived2> | System | NULL | NULL | NULL |    NULL |       1 |  || 2 | DERIVED | <derived3> | System | NULL | NULL | NULL |    NULL |       1 |  || 3 | DERIVED | EMP | System | NULL | NULL | NULL |    NULL |       1 | |+----+-------------+------------+--------+---------------+------+---------+------+------+-------+3 rows in Set (0.01 sec) mysql> 

1.id (JSON name:select_id)
The select identifier, which is the sequential number in the query. If the row is the result of a different row union, the value can be null.

2.select_type (JSON name:none)

List of values for Select_type

Take value	JSON name	Description
Simple		Simple select query (no union, no subquery)
Primary		The outermost query
Union		The second or subsequent SELECT statement in a union
Dependent Union	Dependent (true)	The second or subsequent SELECT statement in the Union, which is dependent on the external query
Union result	Union_result	Result set of Union
Subquery		The first query in a subquery
Dependent subquery	Dependent (true)	The first query in a subquery is dependent on the external query
Derived		Select of derived table (subquery FROM clause)
Materialized	Materialized_from_subquery	Materialized sub-query
Uncacheable subquery	Cacheable (False)	The result can not be cached subquery, the outer query needs to be used to re-execute the time
Uncacheable Union	Cacheable (False)	The second or subsequent subquery in the Union that cannot be cached

3.table (JSON name:table_name)

<unionm,n>: Represents the union;<derivedn> of the result of m rows and n rows;: Represents the result derived from n rows; <subqueryn>: subquery referencing N-rows of materialized

4.partitions (JSON name:partitions)
partitions involved in the query

5.type (JSON name:access_type)
Type of Join

6.possible_keys

The Possible_keys column indicates the indexes that MySQL can use. Note that the column is completely independent of the order of the tables shown in the explain output. This means that some keys in Possible_keys are not actually used in the generated table order.

If the column is null, there is no index associated with it. In this case, you can improve your query performance by examining the WHERE clause to see if it references some columns or columns that fit the index. If so, create an appropriate index and check the query again with explain

7.key (JSON Name:key)

Displays the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null.

To force MySQL to use or not use an index in the Possible_keys column, use the forces Index, using index, or ignore index in the query.

8.key_len (JSON name:key_length)

The Key_len column shows the key lengths that MySQL decides to use. If the key is null, the length is null. The length of the index to use. The shorter the length the better, without loss of accuracy

9.ref (JSON name:ref)

The ref column shows which column or constant is used together with key to select rows from the table.

10.rows (JSON name:rows)

The Rows column shows the number of rows that MySQL must check when it executes the query.

11.filtered (JSON name:filtered)

Percent of filter for the number of rows in the table

Extra.

Contains additional information used by MySQL to perform queries:

When you see the using Filesort and using temporary, the query needs to be optimized.

MySQL explain command explained