When EXPLAIN is used with the
EXTENDED keyword, the output includes a
filtered column not otherwise displayed. This
column indicates the estimated percentage of table rows that
will be filtered by the table condition. In addition, the
statement produces extra information that can be viewed by
issuing a SHOW WARNINGS statement
following the EXPLAIN statement.
The Message value in
SHOW WARNINGS output displays how
the optimizer qualifies table and column names in the
SELECT statement, what the
SELECT looks like after the
application of rewriting and optimization rules, and possibly
other notes about the optimization process. Here is an example:
mysql>EXPLAIN EXTENDED->SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G*************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: index possible_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: index possible_keys: a key: a key_len: 5 ref: NULL rows: 3 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Note Code: 1003 Message: /* 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)
As of MySQL 5.6.3,
EXPLAIN
EXTENDED can be used with
SELECT,
DELETE,
INSERT,
REPLACE, and
UPDATE statements. However, the
following SHOW WARNINGS statement
displays a nonempty result only for
SELECT statements. Before MySQL
5.6.3, EXPLAIN
EXTENDED can be used only with
SELECT statements.
Because the statement displayed by SHOW
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 with
Message values that provide additional
non-SQL explanatory notes about actions taken by the optimizer.
The following list describes special markers that can appear in
EXTENDED output displayed by
SHOW 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 will see
<temporary table>instead.<exists>(query fragment)The subquery predicate is converted to an
EXISTSpredicate and the subquery is transformed so that it can be used together with theEXISTSpredicate.<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 to
expr1, otherwiseexpr2.<is_not_null_test>(expr)A test to verify that the expression does not evaluate to
NULL.<materialize>(query fragment)Subquery materialization is used.
`materialized-subquery`.,col_name`materialized subselect`.col_nameA reference to the column
col_namein 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_stmtThe
SELECTis associated with the row in non-EXTENDEDEXPLAINoutput that has anidvalue ofN.outer_tablessemi join (inner_tables)A semi-join operation.
inner_tablesshows the tables that were not pulled out. See Section 8.13.16.1, “Optimizing Subqueries with Semi-Join Transformations”.<temporary table>This represents an internal temporary table created to cache an intermediate result.
User Comments
Add your own comment.