8.8.3. EXPLAIN EXTENDED Output Format

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: 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)

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.

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:

Spec-Zone.ru - all specs in one place