Movatterモバイル変換

MySQL 5.7 Reference Manual

8.8.3 Extended EXPLAIN Output Format

ForSELECT statements, theEXPLAIN statement produces extra (“extended”) information that is not part ofEXPLAIN output but can be viewed by issuing aSHOW WARNINGS statement followingEXPLAIN. TheMessage value inSHOW WARNINGS output displays how the optimizer qualifies table and column names in theSELECT statement, what theSELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process.

The extended information displayable with aSHOW WARNINGS statement followingEXPLAIN is produced only forSELECT statements.SHOW WARNINGS displays an empty result for other explainable statements (DELETE,INSERT,REPLACE, andUPDATE).

Note

In older MySQL releases, extended information was produced usingEXPLAIN EXTENDED. That syntax is still recognized for backward compatibility but extended output is now enabled by default, so theEXTENDED keyword is superfluous and deprecated. Its use results in a warning; expect it to be removed fromEXPLAIN syntax in a future MySQL release.

Here is an example of extendedEXPLAIN output:

mysql> EXPLAIN       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: t1         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 4          ref: NULL         rows: 4     filtered: 100.00        Extra: Using index*************************** 2. row ***************************           id: 2  select_type: SUBQUERY        table: t2         type: indexpossible_keys: a          key: a      key_len: 5          ref: NULL         rows: 3     filtered: 100.00        Extra: Using index2 rows in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Note   Code: 1003Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,         <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in         ( <materialize> (/* select#2 */ select `test`.`t2`.`a`         from `test`.`t2` where 1 having 1 ),         <primary_index_lookup>(`test`.`t1`.`a` in         <temporary table> on <auto_key>         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a         IN (SELECT t2.a FROM t2)` from `test`.`t1`1 row in set (0.00 sec)

Because the statement displayed bySHOW WARNINGS may contain special markers to provide information about query rewriting or optimizer actions, the statement is not necessarily valid SQL and is not intended to be executed. The output may also include rows withMessage values that provide additional non-SQL explanatory notes about actions taken by the optimizer.

The following list describes special markers that can appear in the extended output displayed bySHOW WARNINGS:

<auto_key>
An automatically generated key for a temporary table.
<cache>(expr)
The expression (such as a scalar subquery) is executed once and the resulting value is saved in memory for later use. For results consisting of multiple values, a temporary table may be created and you might see<temporary table> instead.
<exists>(query fragment)
The subquery predicate is converted to anEXISTS predicate and the subquery is transformed so that it can be used together with theEXISTS predicate.
<in_optimizer>(query fragment)
This is an internal optimizer object with no user significance.
<index_lookup>(query fragment)
The query fragment is processed using an index lookup to find qualifying rows.
<if>(condition,expr1,expr2)
If the condition is true, evaluate toexpr1, otherwiseexpr2.
<is_not_null_test>(expr)
A test to verify that the expression does not evaluate toNULL.
<materialize>(query fragment)
Subquery materialization is used.
`materialized-subquery`.col_name
A reference to the columncol_name in an internal temporary table materialized to hold the result from evaluating a subquery.
<primary_index_lookup>(query fragment)
The query fragment is processed using a primary key lookup to find qualifying rows.
<ref_null_helper>(expr)
This is an internal optimizer object with no user significance.
/* select#N */select_stmt
TheSELECT is associated with the row in non-extendedEXPLAIN output that has anid value ofN.
outer_tables semi join (inner_tables)
A semijoin operation.inner_tables shows the tables that were not pulled out. SeeSection 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”.
<temporary table>
This represents an internal temporary table created to cache an intermediate result.

When some tables are ofconst orsystem type, expressions involving columns from these tables are evaluated early by the optimizer and are not part of the displayed statement. However, withFORMAT=JSON, someconst table accesses are displayed as aref access that uses a const value.

PREV HOME UP NEXT