Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
PDF (RPM) - 26.1Mb
HTML Download (TGZ) - 6.6Mb
HTML Download (Zip) - 6.6Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 168.6Kb
Man Pages (Zip) - 277.1Kb
Info (Gzip) - 2.5Mb
Info (Zip) - 2.5Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Extended EXPLAIN Output Format

8.8.3 Extended EXPLAIN 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 (extended) information that is not part of EXPLAIN output but can be viewed by issuing a SHOW WARNINGS statement following EXPLAIN. 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 of extended EXPLAIN output:

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: DEPENDENT SUBQUERY
        table: t2
         type: index_subquery
possible_keys: a
          key: a
      key_len: 5
          ref: func
         rows: 2
     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 `test`.`t1`.`a` AS `a`,
         <in_optimizer>(`test`.`t1`.`a`,
         <exists>(<index_lookup>(<cache>(`test`.`t1`.`a`)
         in t2 on a checking NULL having
         <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a
         IN (SELECT t2.a FROM t2)` from `test`.`t1`
1 row in set (0.00 sec)

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 the extended output displayed by SHOW WARNINGS:

  • <cache>(expr)

    The expression (such as a scalar subquery) is executed once and the resulting value is saved in memory for later use.

  • <exists>(query fragment)

    The subquery predicate is converted to an EXISTS predicate and the subquery is transformed so that it can be used together with the EXISTS 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.

  • <is_not_null_test>(expr)

    A test to verify that the expression does not evaluate to NULL.

  • <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.

When some tables are of const or system type, expressions involving columns from these tables are evaluated early by the optimizer and are not part of the displayed statement. However, with FORMAT=JSON, some const table accesses are displayed as a ref access that uses a const value.


User Comments
Sign Up Login You must be logged in to post a comment.