<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="explain-extended">
      </a>
      10.8.3 Extended EXPLAIN Output Format
     </h3>
    </div>
   </div>
  </div>
  <p>
   The
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   statement produces
        extra (
   <span class="quote">
    “
    <span class="quote">
     extended
    </span>
    ”
   </span>
   ) information that is not part of
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output but can be viewed
        by issuing a
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW WARNINGS
    </code>
   </a>
   statement following
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   .
        Extended information is available for
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   ,
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   ,
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ,
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   , and
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statements.
  </p>
  <p>
   The
   <code class="literal">
    Message
   </code>
   value in
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW WARNINGS
    </code>
   </a>
   output displays how
        the optimizer qualifies table and column names in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement, what the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   looks like after the
        application of rewriting and optimization rules, and possibly
        other notes about the optimization process.
  </p>
  <p>
   The extended information displayable with a
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW WARNINGS
    </code>
   </a>
   statement following
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   is produced only for
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements.
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW WARNINGS
    </code>
   </a>
   displays an empty
        result for other explainable statements
        (
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   ,
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ,
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   , and
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   ).
  </p>
  <p>
   Here is an example of extended
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26569812"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span>
       <span class="token keyword">SELECT</span> t1<span class="token punctuation">.</span>a<span class="token punctuation">,</span> t1<span class="token punctuation">.</span>a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>a <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> PRIMARY
        table<span class="token punctuation">:</span> t1
         type<span class="token punctuation">:</span> index
possible_keys<span class="token punctuation">:</span> NULL
          key<span class="token punctuation">:</span> PRIMARY
      key_len<span class="token punctuation">:</span> 4
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 4
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using index
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 2. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 2
  select_type<span class="token punctuation">:</span> SUBQUERY
        table<span class="token punctuation">:</span> t2
         type<span class="token punctuation">:</span> index
possible_keys<span class="token punctuation">:</span> a
          key<span class="token punctuation">:</span> a
      key_len<span class="token punctuation">:</span> 5
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 3
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using index
</span><span class="token output">2 rows in set, 1 warning (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  Level<span class="token punctuation">:</span> Note
   Code<span class="token punctuation">:</span> 1003
Message<span class="token punctuation">:</span> /<span class="token punctuation">*</span> select#1 <span class="token punctuation">*</span>/ select `test`.`t1`.`a` AS `a`,
         &lt;in_optimizer&gt;(`test`.`t1`.`a`,`test`.`t1`.`a` in
         ( &lt;materialize&gt; (/<span class="token punctuation">*</span> select#2 <span class="token punctuation">*</span>/ select `test`.`t2`.`a`
         from `test`.`t2` where 1 having 1 ),
         &lt;primary_index_lookup&gt;(`test`.`t1`.`a` in
         &lt;temporary table&gt; on &lt;auto_key&gt;
         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
         IN (SELECT t2.a FROM t2)` from `test`.`t1`
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Because the statement displayed by
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW
        WARNINGS
    </code>
   </a>
   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
   <code class="literal">
    Message
   </code>
   values that provide additional
        non-SQL explanatory notes about actions taken by the optimizer.
  </p>
  <p>
   The following list describes special markers that can appear in
        the extended output displayed by
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW
        WARNINGS
    </code>
   </a>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;auto_key&gt;
      </code>
     </p>
     <p>
      An automatically generated key for a temporary table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;cache&gt;(
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      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
      <code class="literal">
       &lt;temporary
            table&gt;
      </code>
      is shown instead.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;exists&gt;(
       <em class="replaceable">
        <code>
         query
            fragment
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      The subquery predicate is converted to an
      <code class="literal">
       EXISTS
      </code>
      predicate and the subquery is
            transformed so that it can be used together with the
      <code class="literal">
       EXISTS
      </code>
      predicate.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;in_optimizer&gt;(
       <em class="replaceable">
        <code>
         query
            fragment
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      This is an internal optimizer object with no user
            significance.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;index_lookup&gt;(
       <em class="replaceable">
        <code>
         query
            fragment
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      The query fragment is processed using an index lookup to
            find qualifying rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;if&gt;(
       <em class="replaceable">
        <code>
         condition
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         expr1
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         expr2
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      If the condition is true, evaluate to
      <em class="replaceable">
       <code>
        expr1
       </code>
      </em>
      , otherwise
      <em class="replaceable">
       <code>
        expr2
       </code>
      </em>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;is_not_null_test&gt;(
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      A test to verify that the expression does not evaluate to
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;materialize&gt;(
       <em class="replaceable">
        <code>
         query
            fragment
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      Subquery materialization is used.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       `materialized-subquery`.
       <em class="replaceable">
        <code>
         col_name
        </code>
       </em>
      </code>
     </p>
     <p>
      A reference to the column
      <em class="replaceable">
       <code>
        col_name
       </code>
      </em>
      in an internal temporary
            table materialized to hold the result from evaluating a
            subquery.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;primary_index_lookup&gt;(
       <em class="replaceable">
        <code>
         query
            fragment
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      The query fragment is processed using a primary key lookup
            to find qualifying rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;ref_null_helper&gt;(
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      This is an internal optimizer object with no user
            significance.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       /* select#
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       */
       <em class="replaceable">
        <code>
         select_stmt
        </code>
       </em>
      </code>
     </p>
     <p>
      The
      <code class="literal">
       SELECT
      </code>
      is associated with the row in
            non-extended
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      output
            that has an
      <code class="literal">
       id
      </code>
      value of
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         outer_tables
        </code>
       </em>
       semi join
            (
       <em class="replaceable">
        <code>
         inner_tables
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      A semijoin operation.
      <em class="replaceable">
       <code>
        inner_tables
       </code>
      </em>
      shows the tables
            that were not pulled out. See
      <a class="ulink" href="/doc/refman/8.0/en/semijoins.html" target="_top">
       Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       &lt;temporary table&gt;
      </code>
     </p>
     <p>
      This represents an internal temporary table created to cache
            an intermediate result.
     </p>
    </li>
   </ul>
  </div>
  <p>
   When some tables are of
   <a class="link" href="explain-output.html#jointype_const">
    <code class="literal">
     const
    </code>
   </a>
   or
   <a class="link" href="explain-output.html#jointype_system">
    <code class="literal">
     system
    </code>
   </a>
   type, expressions
        involving columns from these tables are evaluated early by the
        optimizer and are not part of the displayed statement. However,
        with
   <code class="literal">
    FORMAT=JSON
   </code>
   , some
   <a class="link" href="explain-output.html#jointype_const">
    <code class="literal">
     const
    </code>
   </a>
   table accesses are
        displayed as a
   <a class="link" href="explain-output.html#jointype_ref">
    <code class="literal">
     ref
    </code>
   </a>
   access
        that uses a const value.
  </p>
 </div>
 <br/>
</div>
