<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="explain-output">
      </a>
      10.8.2 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 provides
        information about how MySQL executes statements.
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   works with
   <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>
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   returns a row of
        information for each table used in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement. It lists the
        tables in the output in the order that MySQL would read them
        while processing the statement. This means that MySQL reads a
        row from the first table, then finds a matching row in the
        second table, and then in the third table, and so on. When all
        tables are processed, MySQL outputs the selected columns and
        backtracks through the table list until a table is found for
        which there are more matching rows. The next row is read from
        this table and the process continues with the next table.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    MySQL Workbench has a Visual Explain capability that provides a
          visual representation of
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    output. See
    <a class="ulink" href="/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html" target="_top">
     Tutorial: Using Explain to Improve Query Performance
    </a>
    .
   </p>
  </div>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="explain-output.html#explain-output-columns" title="EXPLAIN Output Columns">
       EXPLAIN Output Columns
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="explain-output.html#explain-join-types" title="EXPLAIN Join Types">
       EXPLAIN Join Types
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="explain-output.html#explain-extra-information" title="EXPLAIN Extra Information">
       EXPLAIN Extra Information
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="explain-output.html#explain-output-interpretation" title="EXPLAIN Output Interpretation">
       EXPLAIN Output Interpretation
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="explain-output-columns">
       </a>
       EXPLAIN Output Columns
      </h4>
     </div>
    </div>
   </div>
   <p>
    This section describes the output columns produced by
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    . Later sections provide
          additional information about the
    <a class="link" href="explain-output.html#explain-join-types" title="EXPLAIN Join Types">
     <code class="literal">
      type
     </code>
    </a>
    and
    <a class="link" href="explain-output.html#explain-extra-information" title="EXPLAIN Extra Information">
     <code class="literal">
      Extra
     </code>
    </a>
    columns.
   </p>
   <p>
    Each output row from
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    provides information about one table. Each row contains the
          values summarized in
    <a class="xref" href="explain-output.html#explain-output-column-table" title="Table 10.1 EXPLAIN Output Columns">
     Table 10.1, “EXPLAIN Output Columns”
    </a>
    , and described
          in more detail following the table. Column names are shown in
          the table's first column; the second column provides the
          equivalent property name shown in the output when
    <code class="literal">
     FORMAT=JSON
    </code>
    is used.
   </p>
   <div class="table">
    <a name="explain-output-column-table">
    </a>
    <p class="title">
     <b>
      Table 10.1 EXPLAIN Output Columns
     </b>
    </p>
    <div class="table-contents">
     <table summary="Output columns produced by the EXPLAIN statement.">
      <colgroup>
       <col style="width: 25%"/>
       <col style="width: 25%"/>
       <col style="width: 50%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Column
        </th>
        <th scope="col">
         JSON Name
        </th>
        <th scope="col">
         Meaning
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_id">
          <code class="literal">
           id
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          select_id
         </code>
        </td>
        <td>
         The
         <code class="literal">
          SELECT
         </code>
         identifier
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_select_type">
          <code class="literal">
           select_type
          </code>
         </a>
        </th>
        <td>
         None
        </td>
        <td>
         The
         <code class="literal">
          SELECT
         </code>
         type
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_table">
          <code class="literal">
           table
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          table_name
         </code>
        </td>
        <td>
         The table for the output row
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_partitions">
          <code class="literal">
           partitions
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          partitions
         </code>
        </td>
        <td>
         The matching partitions
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_type">
          <code class="literal">
           type
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          access_type
         </code>
        </td>
        <td>
         The join type
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_possible_keys">
          <code class="literal">
           possible_keys
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          possible_keys
         </code>
        </td>
        <td>
         The possible indexes to choose
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_key">
          <code class="literal">
           key
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          key
         </code>
        </td>
        <td>
         The index actually chosen
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_key_len">
          <code class="literal">
           key_len
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          key_length
         </code>
        </td>
        <td>
         The length of the chosen key
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_ref">
          <code class="literal">
           ref
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          ref
         </code>
        </td>
        <td>
         The columns compared to the index
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_rows">
          <code class="literal">
           rows
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          rows
         </code>
        </td>
        <td>
         Estimate of rows to be examined
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_filtered">
          <code class="literal">
           filtered
          </code>
         </a>
        </th>
        <td>
         <code class="literal">
          filtered
         </code>
        </td>
        <td>
         Percentage of rows filtered by table condition
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="explain-output.html#explain_extra">
          <code class="literal">
           Extra
          </code>
         </a>
        </th>
        <td>
         None
        </td>
        <td>
         Additional information
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 586px;">
      <thead>
       <tr>
        <th scope="col" style="width: 146.25px;">
         Column
        </th>
        <th scope="col" style="width: 146.25px;">
         JSON Name
        </th>
        <th scope="col" style="width: 292.5px;">
         Meaning
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     JSON properties which are
     <code class="literal">
      NULL
     </code>
     are not
            displayed in JSON-formatted
     <code class="literal">
      EXPLAIN
     </code>
     output.
    </p>
   </div>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a name="explain_id">
       </a>
       <code class="literal">
        id
       </code>
       (JSON name:
       <code class="literal">
        select_id
       </code>
       )
      </p>
      <p>
       The
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       identifier. This
              is the sequential number of the
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       within the query.
              The value can be
       <code class="literal">
        NULL
       </code>
       if the row refers
              to the union result of other rows. In this case, the
       <code class="literal">
        table
       </code>
       column shows a value like
       <code class="literal">
        &lt;union
        <em class="replaceable">
         <code>
          M
         </code>
        </em>
        ,
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        &gt;
       </code>
       to indicate that the row refers to the union of the rows
              with
       <code class="literal">
        id
       </code>
       values of
       <em class="replaceable">
        <code>
         M
        </code>
       </em>
       and
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_select_type">
       </a>
       <code class="literal">
        select_type
       </code>
       (JSON name: none)
      </p>
      <p>
       The type of
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       , which
              can be any of those shown in the following table. A
              JSON-formatted
       <code class="literal">
        EXPLAIN
       </code>
       exposes the
       <code class="literal">
        SELECT
       </code>
       type as a property of a
       <code class="literal">
        query_block
       </code>
       , unless it is
       <code class="literal">
        SIMPLE
       </code>
       or
       <code class="literal">
        PRIMARY
       </code>
       .
              The JSON names (where applicable) are also shown in the
              table.
      </p>
      <div class="informaltable">
       <table summary="select_type values and the meaning of each value.">
        <colgroup>
         <col style="width: 25%"/>
         <col style="width: 25%"/>
         <col style="width: 50%"/>
        </colgroup>
        <thead>
         <tr>
          <th scope="col">
           <code class="literal">
            select_type
           </code>
           Value
          </th>
          <th scope="col">
           JSON Name
          </th>
          <th scope="col">
           Meaning
          </th>
         </tr>
        </thead>
        <tbody>
         <tr>
          <th scope="row">
           <code class="literal">
            SIMPLE
           </code>
          </th>
          <td>
           None
          </td>
          <td>
           Simple
           <a class="link" href="select.html" title="15.2.13 SELECT Statement">
            <code class="literal">
             SELECT
            </code>
           </a>
           (not using
           <a class="link" href="union.html" title="15.2.18 UNION Clause">
            <code class="literal">
             UNION
            </code>
           </a>
           or subqueries)
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            PRIMARY
           </code>
          </th>
          <td>
           None
          </td>
          <td>
           Outermost
           <a class="link" href="select.html" title="15.2.13 SELECT Statement">
            <code class="literal">
             SELECT
            </code>
           </a>
          </td>
         </tr>
         <tr>
          <th scope="row">
           <a class="link" href="union.html" title="15.2.18 UNION Clause">
            <code class="literal">
             UNION
            </code>
           </a>
          </th>
          <td>
           None
          </td>
          <td>
           Second or later
           <a class="link" href="select.html" title="15.2.13 SELECT Statement">
            <code class="literal">
             SELECT
            </code>
           </a>
           statement in a
           <a class="link" href="union.html" title="15.2.18 UNION Clause">
            <code class="literal">
             UNION
            </code>
           </a>
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            DEPENDENT UNION
           </code>
          </th>
          <td>
           <code class="literal">
            dependent
           </code>
           (
           <code class="literal">
            true
           </code>
           )
          </td>
          <td>
           Second or later
           <a class="link" href="select.html" title="15.2.13 SELECT Statement">
            <code class="literal">
             SELECT
            </code>
           </a>
           statement in a
           <a class="link" href="union.html" title="15.2.18 UNION Clause">
            <code class="literal">
             UNION
            </code>
           </a>
           , dependent on
                    outer query
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            UNION RESULT
           </code>
          </th>
          <td>
           <code class="literal">
            union_result
           </code>
          </td>
          <td>
           Result of a
           <a class="link" href="union.html" title="15.2.18 UNION Clause">
            <code class="literal">
             UNION
            </code>
           </a>
           .
          </td>
         </tr>
         <tr>
          <th scope="row">
           <a class="link" href="optimizer-hints.html#optimizer-hints-subquery" title="Subquery Optimizer Hints">
            <code class="literal">
             SUBQUERY
            </code>
           </a>
          </th>
          <td>
           None
          </td>
          <td>
           First
           <a class="link" href="select.html" title="15.2.13 SELECT Statement">
            <code class="literal">
             SELECT
            </code>
           </a>
           in subquery
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            DEPENDENT SUBQUERY
           </code>
          </th>
          <td>
           <code class="literal">
            dependent
           </code>
           (
           <code class="literal">
            true
           </code>
           )
          </td>
          <td>
           First
           <a class="link" href="select.html" title="15.2.13 SELECT Statement">
            <code class="literal">
             SELECT
            </code>
           </a>
           in subquery, dependent on
                    outer query
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            DERIVED
           </code>
          </th>
          <td>
           None
          </td>
          <td>
           Derived table
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            DEPENDENT DERIVED
           </code>
          </th>
          <td>
           <code class="literal">
            dependent
           </code>
           (
           <code class="literal">
            true
           </code>
           )
          </td>
          <td>
           Derived table dependent on another table
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            MATERIALIZED
           </code>
          </th>
          <td>
           <code class="literal">
            materialized_from_subquery
           </code>
          </td>
          <td>
           Materialized subquery
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            UNCACHEABLE SUBQUERY
           </code>
          </th>
          <td>
           <code class="literal">
            cacheable
           </code>
           (
           <code class="literal">
            false
           </code>
           )
          </td>
          <td>
           A subquery for which the result cannot be cached and must be
                    re-evaluated for each row of the outer query
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            UNCACHEABLE UNION
           </code>
          </th>
          <td>
           <code class="literal">
            cacheable
           </code>
           (
           <code class="literal">
            false
           </code>
           )
          </td>
          <td>
           The second or later select in a
           <a class="link" href="union.html" title="15.2.18 UNION Clause">
            <code class="literal">
             UNION
            </code>
           </a>
           that belongs to an uncacheable subquery (see
           <code class="literal">
            UNCACHEABLE SUBQUERY
           </code>
           )
          </td>
         </tr>
        </tbody>
       </table>
      </div>
      <div class="informaltable">
       <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 431px; width: 709px;">
        <thead>
         <tr>
          <th scope="col" style="width: 167.938px;">
           <code class="literal">
            select_type
           </code>
           Value
          </th>
          <th scope="col" style="width: 218.266px;">
           JSON Name
          </th>
          <th scope="col" style="width: 321.797px;">
           Meaning
          </th>
         </tr>
        </thead>
       </table>
      </div>
      <p>
       <code class="literal">
        DEPENDENT
       </code>
       typically signifies the use
              of a correlated subquery. See
       <a class="xref" href="correlated-subqueries.html" title="15.2.15.7 Correlated Subqueries">
        Section 15.2.15.7, “Correlated Subqueries”
       </a>
       .
      </p>
      <p>
       <code class="literal">
        DEPENDENT SUBQUERY
       </code>
       evaluation differs
              from
       <code class="literal">
        UNCACHEABLE SUBQUERY
       </code>
       evaluation.
              For
       <code class="literal">
        DEPENDENT SUBQUERY
       </code>
       , the subquery is
              re-evaluated only once for each set of different values of
              the variables from its outer context. For
       <code class="literal">
        UNCACHEABLE SUBQUERY
       </code>
       , the subquery is
              re-evaluated for each row of the outer context.
      </p>
      <p>
       When you specify
       <code class="literal">
        FORMAT=JSON
       </code>
       with
       <code class="literal">
        EXPLAIN
       </code>
       , the output has no single
              property directly equivalent to
       <code class="literal">
        select_type
       </code>
       ; the
       <code class="literal">
        query_block
       </code>
       property corresponds to a
              given
       <code class="literal">
        SELECT
       </code>
       . Properties equivalent to
              most of the
       <code class="literal">
        SELECT
       </code>
       subquery types just
              shown are available (an example being
       <code class="literal">
        materialized_from_subquery
       </code>
       for
       <code class="literal">
        MATERIALIZED
       </code>
       ), and are displayed when
              appropriate. There are no JSON equivalents for
       <code class="literal">
        SIMPLE
       </code>
       or
       <code class="literal">
        PRIMARY
       </code>
       .
      </p>
      <p>
       The
       <code class="literal">
        select_type
       </code>
       value for
              non-
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statements
              displays the statement type for affected tables. For
              example,
       <code class="literal">
        select_type
       </code>
       is
       <code class="literal">
        DELETE
       </code>
       for
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       statements.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_table">
       </a>
       <code class="literal">
        table
       </code>
       (JSON name:
       <code class="literal">
        table_name
       </code>
       )
      </p>
      <p>
       The name of the table to which the row of output refers.
              This can also be one of the following values:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           &lt;union
           <em class="replaceable">
            <code>
             M
            </code>
           </em>
           ,
           <em class="replaceable">
            <code>
             N
            </code>
           </em>
           &gt;
          </code>
          :
                  The row refers to the union of the rows with
          <code class="literal">
           id
          </code>
          values of
          <em class="replaceable">
           <code>
            M
           </code>
          </em>
          and
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           &lt;derived
           <em class="replaceable">
            <code>
             N
            </code>
           </em>
           &gt;
          </code>
          :
                  The row refers to the derived table result for the row
                  with an
          <code class="literal">
           id
          </code>
          value of
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          . A derived table may
                  result, for example, from a subquery in the
          <code class="literal">
           FROM
          </code>
          clause.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           &lt;subquery
           <em class="replaceable">
            <code>
             N
            </code>
           </em>
           &gt;
          </code>
          :
                  The row refers to the result of a materialized
                  subquery for the row with an
          <code class="literal">
           id
          </code>
          value of
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          . See
          <a class="xref" href="subquery-materialization.html" title="10.2.2.2 Optimizing Subqueries with Materialization">
           Section 10.2.2.2, “Optimizing Subqueries with Materialization”
          </a>
          .
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_partitions">
       </a>
       <code class="literal">
        partitions
       </code>
       (JSON name:
       <code class="literal">
        partitions
       </code>
       )
      </p>
      <p>
       The partitions from which records would be matched by the
              query. The value is
       <code class="literal">
        NULL
       </code>
       for
              nonpartitioned tables. See
       <a class="xref" href="partitioning-info.html" title="26.3.5 Obtaining Information About Partitions">
        Section 26.3.5, “Obtaining Information About Partitions”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_type">
       </a>
       <code class="literal">
        type
       </code>
       (JSON name:
       <code class="literal">
        access_type
       </code>
       )
      </p>
      <p>
       The join type. For descriptions of the different types,
              see
       <a class="link" href="explain-output.html#explain-join-types" title="EXPLAIN Join Types">
        <code class="literal">
         EXPLAIN
        </code>
        Join Types
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_possible_keys">
       </a>
       <code class="literal">
        possible_keys
       </code>
       (JSON name:
       <code class="literal">
        possible_keys
       </code>
       )
      </p>
      <p>
       The
       <code class="literal">
        possible_keys
       </code>
       column indicates the
              indexes from which MySQL can choose to find the rows in
              this table. Note that this column is totally independent
              of the order of the tables as displayed in the output from
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       . That means that
              some of the keys in
       <code class="literal">
        possible_keys
       </code>
       might
              not be usable in practice with the generated table order.
      </p>
      <p>
       If this column is
       <code class="literal">
        NULL
       </code>
       (or undefined in
              JSON-formatted output), there are no relevant indexes. In
              this case, you may be able to improve the performance of
              your query by examining the
       <code class="literal">
        WHERE
       </code>
       clause to check whether it refers to some column or
              columns that would be suitable for indexing. If so, create
              an appropriate index and check the query with
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       again. See
       <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        Section 15.1.9, “ALTER TABLE Statement”
       </a>
       .
      </p>
      <p>
       To see what indexes a table has, use
       <code class="literal">
        SHOW INDEX
              FROM
        <em class="replaceable">
         <code>
          tbl_name
         </code>
        </em>
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_key">
       </a>
       <code class="literal">
        key
       </code>
       (JSON name:
       <code class="literal">
        key
       </code>
       )
      </p>
      <p>
       The
       <code class="literal">
        key
       </code>
       column indicates the key
              (index) that MySQL actually decided to use. If MySQL
              decides to use one of the
       <code class="literal">
        possible_keys
       </code>
       indexes to look up rows, that index is listed as the key
              value.
      </p>
      <p>
       It is possible that
       <code class="literal">
        key
       </code>
       may name an
              index that is not present in the
       <code class="literal">
        possible_keys
       </code>
       value. This can happen if
              none of the
       <code class="literal">
        possible_keys
       </code>
       indexes are
              suitable for looking up rows, but all the columns selected
              by the query are columns of some other index. That is, the
              named index covers the selected columns, so although it is
              not used to determine which rows to retrieve, an index
              scan is more efficient than a data row scan.
      </p>
      <p>
       For
       <code class="literal">
        InnoDB
       </code>
       , a secondary index might
              cover the selected columns even if the query also selects
              the primary key because
       <code class="literal">
        InnoDB
       </code>
       stores
              the primary key value with each secondary index. If
       <code class="literal">
        key
       </code>
       is
       <code class="literal">
        NULL
       </code>
       , MySQL
              found no index to use for executing the query more
              efficiently.
      </p>
      <p>
       To force MySQL to use or ignore an index listed in the
       <code class="literal">
        possible_keys
       </code>
       column, use
       <code class="literal">
        FORCE INDEX
       </code>
       ,
       <code class="literal">
        USE
              INDEX
       </code>
       , or
       <code class="literal">
        IGNORE INDEX
       </code>
       in
              your query. See
       <a class="xref" href="index-hints.html" title="10.9.4 Index Hints">
        Section 10.9.4, “Index Hints”
       </a>
       .
      </p>
      <p>
       For
       <code class="literal">
        MyISAM
       </code>
       tables, running
       <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
        <code class="literal">
         ANALYZE TABLE
        </code>
       </a>
       helps the
              optimizer choose better indexes. For
       <code class="literal">
        MyISAM
       </code>
       tables,
       <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
        <span class="command">
         <strong>
          myisamchk
              --analyze
         </strong>
        </span>
       </a>
       does the same. See
       <a class="xref" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
        Section 15.7.3.1, “ANALYZE TABLE Statement”
       </a>
       , and
       <a class="xref" href="myisam-table-maintenance.html" title="9.6 MyISAM Table Maintenance and Crash Recovery">
        Section 9.6, “MyISAM Table Maintenance and Crash Recovery”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_key_len">
       </a>
       <code class="literal">
        key_len
       </code>
       (JSON name:
       <code class="literal">
        key_length
       </code>
       )
      </p>
      <p>
       The
       <code class="literal">
        key_len
       </code>
       column indicates the length
              of the key that MySQL decided to use. The value of
       <code class="literal">
        key_len
       </code>
       enables you to determine how
              many parts of a multiple-part key MySQL actually uses. If
              the
       <code class="literal">
        key
       </code>
       column says
       <code class="literal">
        NULL
       </code>
       , the
       <code class="literal">
        key_len
       </code>
       column also says
       <code class="literal">
        NULL
       </code>
       .
      </p>
      <p>
       Due to the key storage format, the key length is one
              greater for a column that can be
       <code class="literal">
        NULL
       </code>
       than for a
       <code class="literal">
        NOT NULL
       </code>
       column.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_ref">
       </a>
       <code class="literal">
        ref
       </code>
       (JSON name:
       <code class="literal">
        ref
       </code>
       )
      </p>
      <p>
       The
       <code class="literal">
        ref
       </code>
       column shows which columns or
              constants are compared to the index named in the
       <code class="literal">
        key
       </code>
       column to select rows from the
              table.
      </p>
      <p>
       If the value is
       <code class="literal">
        func
       </code>
       , the value used is
              the result of some function. To see which function, use
       <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
        <code class="literal">
         SHOW WARNINGS
        </code>
       </a>
       following
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       to see the extended
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       output. The
              function might actually be an operator such as an
              arithmetic operator.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_rows">
       </a>
       <code class="literal">
        rows
       </code>
       (JSON name:
       <code class="literal">
        rows
       </code>
       )
      </p>
      <p>
       The
       <code class="literal">
        rows
       </code>
       column indicates the number of
              rows MySQL believes it must examine to execute the query.
      </p>
      <p>
       For
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       tables, this
              number is an estimate, and may not always be exact.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_filtered">
       </a>
       <code class="literal">
        filtered
       </code>
       (JSON name:
       <code class="literal">
        filtered
       </code>
       )
      </p>
      <p>
       The
       <code class="literal">
        filtered
       </code>
       column indicates an
              estimated percentage of table rows that are filtered by
              the table condition. The maximum value is 100, which means
              no filtering of rows occurred. Values decreasing from 100
              indicate increasing amounts of filtering.
       <code class="literal">
        rows
       </code>
       shows the estimated number of rows
              examined and
       <code class="literal">
        rows
       </code>
       ×
       <code class="literal">
        filtered
       </code>
       shows the number of rows that
              are joined with the following table. For example, if
       <code class="literal">
        rows
       </code>
       is 1000 and
       <code class="literal">
        filtered
       </code>
       is 50.00 (50%), the number of
              rows to be joined with the following table is 1000 ×
              50% = 500.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="explain_extra">
       </a>
       <code class="literal">
        Extra
       </code>
       (JSON name: none)
      </p>
      <p>
       This column contains additional information about how
              MySQL resolves the query. For descriptions of the
              different values, see
       <a class="link" href="explain-output.html#explain-extra-information" title="EXPLAIN Extra Information">
        <code class="literal">
         EXPLAIN
        </code>
        Extra Information
       </a>
       .
      </p>
      <p>
       There is no single JSON property corresponding to the
       <code class="literal">
        Extra
       </code>
       column; however, values that can
              occur in this column are exposed as JSON properties, or as
              the text of the
       <code class="literal">
        message
       </code>
       property.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="explain-join-types">
       </a>
       EXPLAIN Join Types
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <code class="literal">
     type
    </code>
    column of
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    output describes how
          tables are joined. In JSON-formatted output, these are found
          as values of the
    <code class="literal">
     access_type
    </code>
    property. The
          following list describes the join types, ordered from the best
          type to the worst:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a name="jointype_system">
       </a>
       <a class="link" href="explain-output.html#jointype_system">
        <code class="literal">
         system
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225241312">
      </a>
      <a class="indexterm" name="idm46045225239824">
      </a>
      <a class="indexterm" name="idm46045225238336">
      </a>
      <p>
       The table has only one row (= system table). This is a
              special case of the
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       join type.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_const">
       </a>
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225232688">
      </a>
      <a class="indexterm" name="idm46045225231200">
      </a>
      <a class="indexterm" name="idm46045225229712">
      </a>
      <p>
       The table has at most one matching row, which is read at
              the start of the query. Because there is only one row,
              values from the column in this row can be regarded as
              constants by the rest of the optimizer.
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       tables are very
              fast because they are read only once.
      </p>
      <p>
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       is used when you
              compare all parts of a
       <code class="literal">
        PRIMARY KEY
       </code>
       or
       <code class="literal">
        UNIQUE
       </code>
       index to constant values. In the
              following queries,
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       can
              be used as a
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       table:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15552947"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">primary_key</em><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">primary_key_part1</em><span class="token operator">=</span><span class="token number">1</span> <span class="token operator">AND</span> <em class="replaceable">primary_key_part2</em><span class="token operator">=</span><span class="token number">2</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_eq_ref">
       </a>
       <a class="link" href="explain-output.html#jointype_eq_ref">
        <code class="literal">
         eq_ref
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225215904">
      </a>
      <a class="indexterm" name="idm46045225214416">
      </a>
      <p>
       One row is read from this table for each combination of
              rows from the previous tables. Other than the
       <a class="link" href="explain-output.html#jointype_system">
        <code class="literal">
         system
        </code>
       </a>
       and
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       types, this is
              the best possible join type. It is used when all parts of
              an index are used by the join and the index is a
       <code class="literal">
        PRIMARY KEY
       </code>
       or
       <code class="literal">
        UNIQUE NOT
              NULL
       </code>
       index.
      </p>
      <p>
       <a class="link" href="explain-output.html#jointype_eq_ref">
        <code class="literal">
         eq_ref
        </code>
       </a>
       can be used for
              indexed columns that are compared using the
       <code class="literal">
        =
       </code>
       operator. The comparison value can be
              a constant or an expression that uses columns from tables
              that are read before this table. In the following
              examples, MySQL can use an
       <a class="link" href="explain-output.html#jointype_eq_ref">
        <code class="literal">
         eq_ref
        </code>
       </a>
       join to process
       <em class="replaceable">
        <code>
         ref_table
        </code>
       </em>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26718298"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">ref_table</em><span class="token punctuation">,</span><em class="replaceable">other_table</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">ref_table</em><span class="token punctuation">.</span><em class="replaceable">key_column</em><span class="token operator">=</span><em class="replaceable">other_table</em><span class="token punctuation">.</span><span class="token keyword"><em class="replaceable">column</em></span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">ref_table</em><span class="token punctuation">,</span><em class="replaceable">other_table</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">ref_table</em><span class="token punctuation">.</span><em class="replaceable">key_column_part1</em><span class="token operator">=</span><em class="replaceable">other_table</em><span class="token punctuation">.</span><span class="token keyword"><em class="replaceable">column</em></span>
  <span class="token operator">AND</span> <em class="replaceable">ref_table</em><span class="token punctuation">.</span><em class="replaceable">key_column_part2</em><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_ref">
       </a>
       <a class="link" href="explain-output.html#jointype_ref">
        <code class="literal">
         ref
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225194752">
      </a>
      <a class="indexterm" name="idm46045225193296">
      </a>
      <p>
       All rows with matching index values are read from this
              table for each combination of rows from the previous
              tables.
       <a class="link" href="explain-output.html#jointype_ref">
        <code class="literal">
         ref
        </code>
       </a>
       is used if
              the join uses only a leftmost prefix of the key or if the
              key is not a
       <code class="literal">
        PRIMARY KEY
       </code>
       or
       <code class="literal">
        UNIQUE
       </code>
       index (in other words, if the
              join cannot select a single row based on the key value).
              If the key that is used matches only a few rows, this is a
              good join type.
      </p>
      <p>
       <a class="link" href="explain-output.html#jointype_ref">
        <code class="literal">
         ref
        </code>
       </a>
       can be used for
              indexed columns that are compared using the
       <code class="literal">
        =
       </code>
       or
       <code class="literal">
        &lt;=&gt;
       </code>
       operator. In the following examples, MySQL can use a
       <a class="link" href="explain-output.html#jointype_ref">
        <code class="literal">
         ref
        </code>
       </a>
       join to process
       <em class="replaceable">
        <code>
         ref_table
        </code>
       </em>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66345880"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">ref_table</em> <span class="token keyword">WHERE</span> <em class="replaceable">key_column</em><span class="token operator">=</span><em class="replaceable">expr</em><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">ref_table</em><span class="token punctuation">,</span><em class="replaceable">other_table</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">ref_table</em><span class="token punctuation">.</span><em class="replaceable">key_column</em><span class="token operator">=</span><em class="replaceable">other_table</em><span class="token punctuation">.</span><span class="token keyword"><em class="replaceable">column</em></span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">ref_table</em><span class="token punctuation">,</span><em class="replaceable">other_table</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">ref_table</em><span class="token punctuation">.</span><em class="replaceable">key_column_part1</em><span class="token operator">=</span><em class="replaceable">other_table</em><span class="token punctuation">.</span><span class="token keyword"><em class="replaceable">column</em></span>
  <span class="token operator">AND</span> <em class="replaceable">ref_table</em><span class="token punctuation">.</span><em class="replaceable">key_column_part2</em><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_fulltext">
       </a>
       <a class="link" href="explain-output.html#jointype_fulltext">
        <code class="literal">
         fulltext
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225171840">
      </a>
      <a class="indexterm" name="idm46045225170352">
      </a>
      <p>
       The join is performed using a
       <code class="literal">
        FULLTEXT
       </code>
       index.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_ref_or_null">
       </a>
       <a class="link" href="explain-output.html#jointype_ref_or_null">
        <code class="literal">
         ref_or_null
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225165280">
      </a>
      <a class="indexterm" name="idm46045225163792">
      </a>
      <p>
       This join type is like
       <a class="link" href="explain-output.html#jointype_ref">
        <code class="literal">
         ref
        </code>
       </a>
       , but with the
              addition that MySQL does an extra search for rows that
              contain
       <code class="literal">
        NULL
       </code>
       values. This join type
              optimization is used most often in resolving subqueries.
              In the following examples, MySQL can use a
       <a class="link" href="explain-output.html#jointype_ref_or_null">
        <code class="literal">
         ref_or_null
        </code>
       </a>
       join to
              process
       <em class="replaceable">
        <code>
         ref_table
        </code>
       </em>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74694256"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">ref_table</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_column</em><span class="token operator">=</span><em class="replaceable">expr</em> <span class="token operator">OR</span> <em class="replaceable">key_column</em> <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       See
       <a class="xref" href="is-null-optimization.html" title="10.2.1.15 IS NULL Optimization">
        Section 10.2.1.15, “IS NULL Optimization”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_index_merge">
       </a>
       <a class="link" href="explain-output.html#jointype_index_merge">
        <code class="literal">
         index_merge
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225151760">
      </a>
      <a class="indexterm" name="idm46045225150272">
      </a>
      <p>
       This join type indicates that the Index Merge optimization
              is used. In this case, the
       <code class="literal">
        key
       </code>
       column
              in the output row contains a list of indexes used, and
       <code class="literal">
        key_len
       </code>
       contains a list of the longest
              key parts for the indexes used. For more information, see
       <a class="xref" href="index-merge-optimization.html" title="10.2.1.3 Index Merge Optimization">
        Section 10.2.1.3, “Index Merge Optimization”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_unique_subquery">
       </a>
       <a class="link" href="explain-output.html#jointype_unique_subquery">
        <code class="literal">
         unique_subquery
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225143536">
      </a>
      <a class="indexterm" name="idm46045225142048">
      </a>
      <p>
       This type replaces
       <a class="link" href="explain-output.html#jointype_eq_ref">
        <code class="literal">
         eq_ref
        </code>
       </a>
       for some
       <code class="literal">
        IN
       </code>
       subqueries of the following form:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39959782"><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 keyword"><em class="replaceable">value</em></span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">primary_key</em> <span class="token keyword">FROM</span> <em class="replaceable">single_table</em> <span class="token keyword">WHERE</span> <em class="replaceable">some_expr</em><span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       <a class="link" href="explain-output.html#jointype_unique_subquery">
        <code class="literal">
         unique_subquery
        </code>
       </a>
       is just
              an index lookup function that replaces the subquery
              completely for better efficiency.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_index_subquery">
       </a>
       <a class="link" href="explain-output.html#jointype_index_subquery">
        <code class="literal">
         index_subquery
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225131488">
      </a>
      <a class="indexterm" name="idm46045225130000">
      </a>
      <p>
       This join type is similar to
       <a class="link" href="explain-output.html#jointype_unique_subquery">
        <code class="literal">
         unique_subquery
        </code>
       </a>
       . It
              replaces
       <code class="literal">
        IN
       </code>
       subqueries, but it works
              for nonunique indexes in subqueries of the following form:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61428563"><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 keyword"><em class="replaceable">value</em></span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">key_column</em> <span class="token keyword">FROM</span> <em class="replaceable">single_table</em> <span class="token keyword">WHERE</span> <em class="replaceable">some_expr</em><span class="token punctuation">)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_range">
       </a>
       <a class="link" href="explain-output.html#jointype_range">
        <code class="literal">
         range
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225121104">
      </a>
      <a class="indexterm" name="idm46045225119616">
      </a>
      <p>
       Only rows that are in a given range are retrieved, using
              an index to select the rows. The
       <code class="literal">
        key
       </code>
       column in the output row indicates which index is used.
              The
       <code class="literal">
        key_len
       </code>
       contains the longest key
              part that was used. The
       <code class="literal">
        ref
       </code>
       column is
       <code class="literal">
        NULL
       </code>
       for this type.
      </p>
      <p>
       <a class="link" href="explain-output.html#jointype_range">
        <code class="literal">
         range
        </code>
       </a>
       can be used when
              a key column is compared to a constant using any of the
       <a class="link" href="comparison-operators.html#operator_equal">
        <code class="literal">
         =
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_not-equal">
        <code class="literal">
         &lt;&gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_greater-than">
        <code class="literal">
         &gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_greater-than-or-equal">
        <code class="literal">
         &gt;=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_less-than">
        <code class="literal">
         &lt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_less-than-or-equal">
        <code class="literal">
         &lt;=
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_is-null">
        <code class="literal">
         IS NULL
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_equal-to">
        <code class="literal">
         &lt;=&gt;
        </code>
       </a>
       ,
       <a class="link" href="comparison-operators.html#operator_between">
        <code class="literal">
         BETWEEN
        </code>
       </a>
       ,
       <a class="link" href="string-comparison-functions.html#operator_like">
        <code class="literal">
         LIKE
        </code>
       </a>
       , or
       <a class="link" href="comparison-operators.html#operator_in">
        <code class="literal">
         IN()
        </code>
       </a>
       operators:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31856867"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_column</em> <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_column</em> <span class="token operator">BETWEEN</span> <span class="token number">10</span> <span class="token operator">and</span> <span class="token number">20</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_column</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em> <span class="token operator">=</span> <span class="token number">10</span> <span class="token operator">AND</span> <em class="replaceable">key_part2</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_index">
       </a>
       <a class="link" href="explain-output.html#jointype_index">
        <code class="literal">
         index
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225091952">
      </a>
      <a class="indexterm" name="idm46045225090464">
      </a>
      <p>
       The
       <code class="literal">
        index
       </code>
       join type is the same as
       <a class="link" href="explain-output.html#jointype_all">
        <code class="literal">
         ALL
        </code>
       </a>
       , except that the
              index tree is scanned. This occurs two ways:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If the index is a covering index for the queries and
                  can be used to satisfy all data required from the
                  table, only the index tree is scanned. In this case,
                  the
          <code class="literal">
           Extra
          </code>
          column says
          <code class="literal">
           Using index
          </code>
          . An index-only scan
                  usually is faster than
          <a class="link" href="explain-output.html#jointype_all">
           <code class="literal">
            ALL
           </code>
          </a>
          because the
                  size of the index usually is smaller than the table
                  data.
         </p>
        </li>
        <li class="listitem">
         <p>
          A full table scan is performed using reads from the
                  index to look up data rows in index order.
          <code class="literal">
           Uses index
          </code>
          does not appear in the
          <code class="literal">
           Extra
          </code>
          column.
         </p>
        </li>
       </ul>
      </div>
      <p>
       MySQL can use this join type when the query uses only
              columns that are part of a single index.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="jointype_all">
       </a>
       <a class="link" href="explain-output.html#jointype_all">
        <code class="literal">
         ALL
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045225076960">
      </a>
      <a class="indexterm" name="idm46045225075504">
      </a>
      <p>
       A full table scan is done for each combination of rows
              from the previous tables. This is normally not good if the
              table is the first table not marked
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       , and usually
       <span class="emphasis">
        <em>
         very
        </em>
       </span>
       bad in all other cases.
              Normally, you can avoid
       <a class="link" href="explain-output.html#jointype_all">
        <code class="literal">
         ALL
        </code>
       </a>
       by adding indexes
              that enable row retrieval from the table based on constant
              values or column values from earlier tables.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="explain-extra-information">
       </a>
       EXPLAIN Extra Information
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <code class="literal">
     Extra
    </code>
    column of
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    output contains
          additional information about how MySQL resolves the query. The
          following list explains the values that can appear in this
          column. Each item also indicates for JSON-formatted output
          which property displays the
    <code class="literal">
     Extra
    </code>
    value.
          For some of these, there is a specific property. The others
          display as the text of the
    <code class="literal">
     message
    </code>
    property.
   </p>
   <p>
    If you want to make your queries as fast as possible, look out
          for
    <code class="literal">
     Extra
    </code>
    column values of
    <code class="literal">
     Using
          filesort
    </code>
    and
    <code class="literal">
     Using temporary
    </code>
    , or,
          in JSON-formatted
    <code class="literal">
     EXPLAIN
    </code>
    output, for
    <code class="literal">
     using_filesort
    </code>
    and
    <code class="literal">
     using_temporary_table
    </code>
    properties equal to
    <code class="literal">
     true
    </code>
    .
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        Backward index scan
       </code>
       (JSON:
       <code class="literal">
        backward_index_scan
       </code>
       )
      </p>
      <p>
       The optimizer is able to use a descending index on an
       <code class="literal">
        InnoDB
       </code>
       table. Shown together with
       <code class="literal">
        Using index
       </code>
       . For more information, see
       <a class="xref" href="descending-indexes.html" title="10.3.13 Descending Indexes">
        Section 10.3.13, “Descending Indexes”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Child of '
        <em class="replaceable">
         <code>
          table
         </code>
        </em>
        '
              pushed join@1
       </code>
       (JSON:
       <code class="literal">
        message
       </code>
       text)
      </p>
      <p>
       This table is referenced as the child of
       <em class="replaceable">
        <code>
         table
        </code>
       </em>
       in a join that can be
              pushed down to the NDB kernel. Applies only in NDB
              Cluster, when pushed-down joins are enabled. See the
              description of the
       <a class="link" href="mysql-cluster-options-variables.html#sysvar_ndb_join_pushdown">
        <code class="literal">
         ndb_join_pushdown
        </code>
       </a>
       server
              system variable for more information and examples.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        const row not found
       </code>
       (JSON property:
       <code class="literal">
        const_row_not_found
       </code>
       )
      </p>
      <p>
       For a query such as
       <code class="literal">
        SELECT ... FROM
        <em class="replaceable">
         <code>
          tbl_name
         </code>
        </em>
       </code>
       , the table
              was empty.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Deleting all rows
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       For
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       , some storage
              engines (such as
       <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
        <code class="literal">
         MyISAM
        </code>
       </a>
       )
              support a handler method that removes all table rows in a
              simple and fast way. This
       <code class="literal">
        Extra
       </code>
       value
              is displayed if the engine uses this optimization.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Distinct
       </code>
       (JSON property:
       <code class="literal">
        distinct
       </code>
       )
      </p>
      <p>
       MySQL is looking for distinct values, so it stops
              searching for more rows for the current row combination
              after it has found the first matching row.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        FirstMatch(
        <em class="replaceable">
         <code>
          tbl_name
         </code>
        </em>
        )
       </code>
       (JSON property:
       <code class="literal">
        first_match
       </code>
       )
      </p>
      <p>
       The semijoin FirstMatch join shortcutting strategy is used
              for
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Full scan on NULL key
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       This occurs for subquery optimization as a fallback
              strategy when the optimizer cannot use an index-lookup
              access method.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Impossible HAVING
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       The
       <code class="literal">
        HAVING
       </code>
       clause is always false and
              cannot select any rows.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Impossible WHERE
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       The
       <code class="literal">
        WHERE
       </code>
       clause is always false and
              cannot select any rows.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Impossible WHERE noticed after reading const
              tables
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       MySQL has read all
       <a class="link" href="explain-output.html#jointype_const">
        <code class="literal">
         const
        </code>
       </a>
       (and
       <a class="link" href="explain-output.html#jointype_system">
        <code class="literal">
         system
        </code>
       </a>
       ) tables and
              notice that the
       <code class="literal">
        WHERE
       </code>
       clause is always
              false.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        LooseScan(
        <em class="replaceable">
         <code>
          m
         </code>
        </em>
        ..
        <em class="replaceable">
         <code>
          n
         </code>
        </em>
        )
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       The semijoin LooseScan strategy is used.
       <em class="replaceable">
        <code>
         m
        </code>
       </em>
       and
       <em class="replaceable">
        <code>
         n
        </code>
       </em>
       are key part numbers.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        No matching min/max row
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       No row satisfies the condition for a query such as
       <code class="literal">
        SELECT MIN(...) FROM ... WHERE
        <em class="replaceable">
         <code>
          condition
         </code>
        </em>
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        no matching row in const table
       </code>
       (JSON
              property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       For a query with a join, there was an empty table or a
              table with no rows satisfying a unique index condition.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        No matching rows after partition
              pruning
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       For
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       or
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       , the optimizer found
              nothing to delete or update after partition pruning. It is
              similar in meaning to
       <code class="literal">
        Impossible WHERE
       </code>
       for
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statements.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        No tables used
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       The query has no
       <code class="literal">
        FROM
       </code>
       clause, or has a
       <code class="literal">
        FROM DUAL
       </code>
       clause.
      </p>
      <p>
       For
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       or
       <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
        <code class="literal">
         REPLACE
        </code>
       </a>
       statements,
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       displays this value
              when there is no
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part. For example, it appears for
       <code class="literal">
        EXPLAIN INSERT
              INTO t VALUES(10)
       </code>
       because that is equivalent to
       <code class="literal">
        EXPLAIN INSERT INTO t SELECT 10 FROM
              DUAL
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Not exists
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       MySQL was able to do a
       <code class="literal">
        LEFT JOIN
       </code>
       optimization on the query and does not examine more rows
              in this table for the previous row combination after it
              finds one row that matches the
       <code class="literal">
        LEFT
              JOIN
       </code>
       criteria. Here is an example of the type of
              query that can be optimized this way:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96169046"><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 keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>id<span class="token operator">=</span>t2<span class="token punctuation">.</span>id
  <span class="token keyword">WHERE</span> t2<span class="token punctuation">.</span>id <span class="token operator">IS</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Assume that
       <code class="literal">
        t2.id
       </code>
       is defined as
       <code class="literal">
        NOT NULL
       </code>
       . In this case, MySQL scans
       <code class="literal">
        t1
       </code>
       and looks up the rows in
       <code class="literal">
        t2
       </code>
       using the values of
       <code class="literal">
        t1.id
       </code>
       . If MySQL finds a matching row in
       <code class="literal">
        t2
       </code>
       , it knows that
       <code class="literal">
        t2.id
       </code>
       can never be
       <code class="literal">
        NULL
       </code>
       , and does not scan through the
              rest of the rows in
       <code class="literal">
        t2
       </code>
       that have the
              same
       <code class="literal">
        id
       </code>
       value. In other words, for each
              row in
       <code class="literal">
        t1
       </code>
       , MySQL needs to do only a
              single lookup in
       <code class="literal">
        t2
       </code>
       , regardless of how
              many rows actually match in
       <code class="literal">
        t2
       </code>
       .
      </p>
      <p>
       This can also indicate that a
       <code class="literal">
        WHERE
       </code>
       condition of the form
       <code class="literal">
        NOT IN
              (
        <em class="replaceable">
         <code>
          subquery
         </code>
        </em>
        )
       </code>
       or
       <code class="literal">
        NOT EXISTS
              (
        <em class="replaceable">
         <code>
          subquery
         </code>
        </em>
        )
       </code>
       has been
              transformed internally into an antijoin. This removes the
              subquery and brings its tables into the plan for the
              topmost query, providing improved cost planning. By
              merging semijoins and antijoins, the optimizer can reorder
              tables in the execution plan more freely, in some cases
              resulting in a faster plan.
      </p>
      <p>
       You can see when an antijoin transformation is performed
              for a given query by checking the
       <code class="literal">
        Message
       </code>
       column from
       <code class="literal">
        SHOW
              WARNINGS
       </code>
       following execution of
       <code class="literal">
        EXPLAIN
       </code>
       , or in the output of
       <code class="literal">
        EXPLAIN FORMAT=TREE
       </code>
       .
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        An antijoin is the complement of a semijoin
        <code class="literal">
         <em class="replaceable">
          <code>
           table_a
          </code>
         </em>
         JOIN
         <em class="replaceable">
          <code>
           table_b
          </code>
         </em>
         ON
         <em class="replaceable">
          <code>
           condition
          </code>
         </em>
        </code>
        . The
                antijoin returns all rows from
        <em class="replaceable">
         <code>
          table_a
         </code>
        </em>
        for which there is
        <span class="emphasis">
         <em>
          no
         </em>
        </span>
        row in
        <em class="replaceable">
         <code>
          table_b
         </code>
        </em>
        which matches
        <em class="replaceable">
         <code>
          condition
         </code>
        </em>
        .
       </p>
      </div>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Plan isn't ready yet
       </code>
       (JSON property:
              none)
      </p>
      <p>
       This value occurs with
       <a class="link" href="explain-for-connection.html" title="10.8.4 Obtaining Execution Plan Information for a Named Connection">
        <code class="literal">
         EXPLAIN FOR
              CONNECTION
        </code>
       </a>
       when the optimizer has not finished
              creating the execution plan for the statement executing in
              the named connection. If execution plan output comprises
              multiple lines, any or all of them could have this
       <code class="literal">
        Extra
       </code>
       value, depending on the progress
              of the optimizer in determining the full execution plan.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Range checked for each record (index map:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        )
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       MySQL found no good index to use, but found that some of
              indexes might be used after column values from preceding
              tables are known. For each row combination in the
              preceding tables, MySQL checks whether it is possible to
              use a
       <a class="link" href="explain-output.html#jointype_range">
        <code class="literal">
         range
        </code>
       </a>
       or
       <a class="link" href="explain-output.html#jointype_index_merge">
        <code class="literal">
         index_merge
        </code>
       </a>
       access
              method to retrieve rows. This is not very fast, but is
              faster than performing a join with no index at all. The
              applicability criteria are as described in
       <a class="xref" href="range-optimization.html" title="10.2.1.2 Range Optimization">
        Section 10.2.1.2, “Range Optimization”
       </a>
       , and
       <a class="xref" href="index-merge-optimization.html" title="10.2.1.3 Index Merge Optimization">
        Section 10.2.1.3, “Index Merge Optimization”
       </a>
       , with the
              exception that all column values for the preceding table
              are known and considered to be constants.
      </p>
      <p>
       Indexes are numbered beginning with 1, in the same order
              as shown by
       <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
        <code class="literal">
         SHOW INDEX
        </code>
       </a>
       for
              the table. The index map value
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       is a bitmask value that
              indicates which indexes are candidates. For example, a
              value of
       <code class="literal">
        0x19
       </code>
       (binary 11001) means that
              indexes 1, 4, and 5 are considered.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Recursive
       </code>
       (JSON property:
       <code class="literal">
        recursive
       </code>
       )
      </p>
      <p>
       This indicates that the row applies to the recursive
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       part of a recursive
              common table expression. See
       <a class="xref" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
        Section 15.2.20, “WITH (Common Table Expressions)”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Rematerialize
       </code>
       (JSON property:
       <code class="literal">
        rematerialize
       </code>
       )
      </p>
      <a class="indexterm" name="idm46045224938320">
      </a>
      <a class="indexterm" name="idm46045224936832">
      </a>
      <p>
       <code class="literal">
        Rematerialize (X,...)
       </code>
       is displayed in
              the
       <code class="literal">
        EXPLAIN
       </code>
       row for table
       <code class="literal">
        T
       </code>
       , where
       <code class="literal">
        X
       </code>
       is any
              lateral derived table whose rematerialization is triggered
              when a new row of
       <code class="literal">
        T
       </code>
       is read. For
              example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53047838"><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 keyword">SELECT</span>
  <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">FROM</span>
  t<span class="token punctuation">,</span>
  <span class="token keyword">LATERAL</span> <span class="token punctuation">(</span><em class="replaceable">derived <span class="token keyword">table</span> that refers <span class="token keyword">to</span> t</em><span class="token punctuation">)</span> <span class="token keyword">AS</span> dt
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
      </div>
      <p>
       The content of the derived table is rematerialized to
              bring it up to date each time a new row of
       <code class="literal">
        t
       </code>
       is processed by the top query.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Scanned
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
        databases
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       This indicates how many directory scans the server
              performs when processing a query for
       <code class="literal">
        INFORMATION_SCHEMA
       </code>
       tables, as described
              in
       <a class="xref" href="information-schema-optimization.html" title="10.2.3 Optimizing INFORMATION_SCHEMA Queries">
        Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”
       </a>
       . The
              value of
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
       can be 0, 1, or
       <code class="literal">
        all
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Select tables optimized away
       </code>
       (JSON
              property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       The optimizer determined 1) that at most one row should be
              returned, and 2) that to produce this row, a deterministic
              set of rows must be read. When the rows to be read can be
              read during the optimization phase (for example, by
              reading index rows), there is no need to read any tables
              during query execution.
      </p>
      <p>
       The first condition is fulfilled when the query is
              implicitly grouped (contains an aggregate function but no
       <code class="literal">
        GROUP BY
       </code>
       clause). The second condition
              is fulfilled when one row lookup is performed per index
              used. The number of indexes read determines the number of
              rows to read.
      </p>
      <p>
       Consider the following implicitly grouped query:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa6910043"><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 keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>c1<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">MIN</span><span class="token punctuation">(</span>c2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Suppose that
       <code class="literal">
        MIN(c1)
       </code>
       can be retrieved
              by reading one index row and
       <code class="literal">
        MIN(c2)
       </code>
       can be retrieved by reading one row from a different
              index. That is, for each column
       <code class="literal">
        c1
       </code>
       and
       <code class="literal">
        c2
       </code>
       , there exists an index where the
              column is the first column of the index. In this case, one
              row is returned, produced by reading two deterministic
              rows.
      </p>
      <p>
       This
       <code class="literal">
        Extra
       </code>
       value does not occur if the
              rows to read are not deterministic. Consider this query:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa29006230"><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 keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>c2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">&lt;=</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Suppose that
       <code class="literal">
        (c1, c2)
       </code>
       is a covering
              index. Using this index, all rows with
       <code class="literal">
        c1 &lt;=
              10
       </code>
       must be scanned to find the minimum
       <code class="literal">
        c2
       </code>
       value. By contrast, consider this
              query:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa315111"><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 keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>c2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       In this case, the first index row with
       <code class="literal">
        c1 =
              10
       </code>
       contains the minimum
       <code class="literal">
        c2
       </code>
       value. Only one row must be read to produce the returned
              row.
      </p>
      <p>
       For storage engines that maintain an exact row count per
              table (such as
       <code class="literal">
        MyISAM
       </code>
       , but not
       <code class="literal">
        InnoDB
       </code>
       ), this
       <code class="literal">
        Extra
       </code>
       value can occur for
       <code class="literal">
        COUNT(*)
       </code>
       queries
              for which the
       <code class="literal">
        WHERE
       </code>
       clause is missing
              or always true and there is no
       <code class="literal">
        GROUP BY
       </code>
       clause. (This is an instance of an implicitly grouped
              query where the storage engine influences whether a
              deterministic number of rows can be read.)
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Skip_open_table
       </code>
       ,
       <code class="literal">
        Open_frm_only
       </code>
       ,
       <code class="literal">
        Open_full_table
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       These values indicate file-opening optimizations that
              apply to queries for
       <code class="literal">
        INFORMATION_SCHEMA
       </code>
       tables.
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           Skip_open_table
          </code>
          : Table files do not
                  need to be opened. The information is already
                  available from the data dictionary.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           Open_frm_only
          </code>
          : Only the data
                  dictionary need be read for table information.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           Open_full_table
          </code>
          : Unoptimized
                  information lookup. Table information must be read
                  from the data dictionary and by reading table files.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Start temporary
       </code>
       ,
       <code class="literal">
        End
              temporary
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       This indicates temporary table use for the semijoin
              Duplicate Weedout strategy.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        unique row not found
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       For a query such as
       <code class="literal">
        SELECT ... FROM
        <em class="replaceable">
         <code>
          tbl_name
         </code>
        </em>
       </code>
       , no rows
              satisfy the condition for a
       <code class="literal">
        UNIQUE
       </code>
       index or
       <code class="literal">
        PRIMARY KEY
       </code>
       on the table.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using filesort
       </code>
       (JSON property:
       <code class="literal">
        using_filesort
       </code>
       )
      </p>
      <p>
       MySQL must do an extra pass to find out how to retrieve
              the rows in sorted order. The sort is done by going
              through all rows according to the join type and storing
              the sort key and pointer to the row for all rows that
              match the
       <code class="literal">
        WHERE
       </code>
       clause. The keys then
              are sorted and the rows are retrieved in sorted order. See
       <a class="xref" href="order-by-optimization.html" title="10.2.1.16 ORDER BY Optimization">
        Section 10.2.1.16, “ORDER BY Optimization”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using index
       </code>
       (JSON property:
       <code class="literal">
        using_index
       </code>
       )
      </p>
      <p>
       The column information is retrieved from the table using
              only information in the index tree without having to do an
              additional seek to read the actual row. This strategy can
              be used when the query uses only columns that are part of
              a single index.
      </p>
      <p>
       For
       <code class="literal">
        InnoDB
       </code>
       tables that have a
              user-defined clustered index, that index can be used even
              when
       <code class="literal">
        Using index
       </code>
       is absent from the
       <code class="literal">
        Extra
       </code>
       column. This is the case if
       <code class="literal">
        type
       </code>
       is
       <a class="link" href="explain-output.html#jointype_index">
        <code class="literal">
         index
        </code>
       </a>
       and
       <code class="literal">
        key
       </code>
       is
       <code class="literal">
        PRIMARY
       </code>
       .
      </p>
      <p>
       Information about any covering indexes used is shown for
       <code class="literal">
        EXPLAIN FORMAT=TRADITIONAL
       </code>
       and
       <code class="literal">
        EXPLAIN FORMAT=JSON
       </code>
       . It is also shown
              for
       <code class="literal">
        EXPLAIN FORMAT=TREE
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using index condition
       </code>
       (JSON property:
       <code class="literal">
        using_index_condition
       </code>
       )
      </p>
      <p>
       Tables are read by accessing index tuples and testing them
              first to determine whether to read full table rows. In
              this way, index information is used to defer (
       <span class="quote">
        “
        <span class="quote">
         push
              down
        </span>
        ”
       </span>
       ) reading full table rows unless it is
              necessary. See
       <a class="xref" href="index-condition-pushdown-optimization.html" title="10.2.1.6 Index Condition Pushdown Optimization">
        Section 10.2.1.6, “Index Condition Pushdown Optimization”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using index for group-by
       </code>
       (JSON
              property:
       <code class="literal">
        using_index_for_group_by
       </code>
       )
      </p>
      <p>
       Similar to the
       <code class="literal">
        Using index
       </code>
       table access
              method,
       <code class="literal">
        Using index for group-by
       </code>
       indicates that MySQL found an index that can be used to
              retrieve all columns of a
       <code class="literal">
        GROUP BY
       </code>
       or
       <code class="literal">
        DISTINCT
       </code>
       query without any extra disk
              access to the actual table. Additionally, the index is
              used in the most efficient way so that for each group,
              only a few index entries are read. For details, see
       <a class="xref" href="group-by-optimization.html" title="10.2.1.17 GROUP BY Optimization">
        Section 10.2.1.17, “GROUP BY Optimization”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using index for skip scan
       </code>
       (JSON
              property:
       <code class="literal">
        using_index_for_skip_scan
       </code>
       )
      </p>
      <p>
       Indicates that the Skip Scan access method is used. See
       <a class="xref" href="range-optimization.html#range-access-skip-scan" title="Skip Scan Range Access Method">
        Skip Scan Range Access Method
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using join buffer (Block Nested Loop)
       </code>
       ,
       <code class="literal">
        Using join buffer (Batched Key Access)
       </code>
       ,
       <code class="literal">
        Using join buffer (hash join)
       </code>
       (JSON
              property:
       <code class="literal">
        using_join_buffer
       </code>
       )
      </p>
      <p>
       Tables from earlier joins are read in portions into the
              join buffer, and then their rows are used from the buffer
              to perform the join with the current table.
       <code class="literal">
        (Block Nested Loop)
       </code>
       indicates use of
              the Block Nested-Loop algorithm,
       <code class="literal">
        (Batched Key
              Access)
       </code>
       indicates use of the Batched Key Access
              algorithm, and
       <code class="literal">
        (hash join)
       </code>
       indicates
              use of a hash join. That is, the keys from the table on
              the preceding line of the
       <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
        <code class="literal">
         EXPLAIN
        </code>
       </a>
       output are
              buffered, and the matching rows are fetched in batches
              from the table represented by the line in which
       <code class="literal">
        Using join buffer
       </code>
       appears.
      </p>
      <p>
       In JSON-formatted output, the value of
       <code class="literal">
        using_join_buffer
       </code>
       is always one of
       <code class="literal">
        Block Nested Loop
       </code>
       ,
       <code class="literal">
        Batched Key
              Access
       </code>
       , or
       <code class="literal">
        hash join
       </code>
       .
      </p>
      <p>
       For more information about hash joins, see
       <a class="xref" href="hash-joins.html" title="10.2.1.4 Hash Join Optimization">
        Section 10.2.1.4, “Hash Join Optimization”
       </a>
       .
      </p>
      <p>
       See
       <a class="xref" href="bnl-bka-optimization.html#bka-optimization" title="Batched Key Access Joins">
        Batched Key Access Joins
       </a>
       , for information
              about the Batched Key Access algorithm.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using MRR
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       Tables are read using the Multi-Range Read optimization
              strategy. See
       <a class="xref" href="mrr-optimization.html" title="10.2.1.11 Multi-Range Read Optimization">
        Section 10.2.1.11, “Multi-Range Read Optimization”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using sort_union(...)
       </code>
       ,
       <code class="literal">
        Using
              union(...)
       </code>
       ,
       <code class="literal">
        Using
              intersect(...)
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       These indicate the particular algorithm showing how index
              scans are merged for the
       <a class="link" href="explain-output.html#jointype_index_merge">
        <code class="literal">
         index_merge
        </code>
       </a>
       join type.
              See
       <a class="xref" href="index-merge-optimization.html" title="10.2.1.3 Index Merge Optimization">
        Section 10.2.1.3, “Index Merge Optimization”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using temporary
       </code>
       (JSON property:
       <code class="literal">
        using_temporary_table
       </code>
       )
      </p>
      <p>
       To resolve the query, MySQL needs to create a temporary
              table to hold the result. This typically happens if the
              query contains
       <code class="literal">
        GROUP BY
       </code>
       and
       <code class="literal">
        ORDER BY
       </code>
       clauses that list columns
              differently.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using where
       </code>
       (JSON property:
       <code class="literal">
        attached_condition
       </code>
       )
      </p>
      <p>
       A
       <code class="literal">
        WHERE
       </code>
       clause is used to restrict
              which rows to match against the next table or send to the
              client. Unless you specifically intend to fetch or examine
              all rows from the table, you may have something wrong in
              your query if the
       <code class="literal">
        Extra
       </code>
       value is not
       <code class="literal">
        Using where
       </code>
       and the table join type is
       <a class="link" href="explain-output.html#jointype_all">
        <code class="literal">
         ALL
        </code>
       </a>
       or
       <a class="link" href="explain-output.html#jointype_index">
        <code class="literal">
         index
        </code>
       </a>
       .
      </p>
      <p>
       <code class="literal">
        Using where
       </code>
       has no direct counterpart
              in JSON-formatted output; the
       <code class="literal">
        attached_condition
       </code>
       property contains
              any
       <code class="literal">
        WHERE
       </code>
       condition used.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Using where with pushed condition
       </code>
       (JSON
              property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       This item applies to
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       tables
       <span class="emphasis">
        <em>
         only
        </em>
       </span>
       . It means that NDB
              Cluster is using the Condition Pushdown optimization to
              improve the efficiency of a direct comparison between a
              nonindexed column and a constant. In such cases, the
              condition is
       <span class="quote">
        “
        <span class="quote">
         pushed down
        </span>
        ”
       </span>
       to the
              cluster's data nodes and is evaluated on all data
              nodes simultaneously. This eliminates the need to send
              nonmatching rows over the network, and can speed up such
              queries by a factor of 5 to 10 times over cases where
              Condition Pushdown could be but is not used. For more
              information, see
       <a class="xref" href="engine-condition-pushdown-optimization.html" title="10.2.1.5 Engine Condition Pushdown Optimization">
        Section 10.2.1.5, “Engine Condition Pushdown Optimization”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Zero limit
       </code>
       (JSON property:
       <code class="literal">
        message
       </code>
       )
      </p>
      <p>
       The query had a
       <code class="literal">
        LIMIT 0
       </code>
       clause and
              cannot select any rows.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="explain-output-interpretation">
       </a>
       EXPLAIN Output Interpretation
      </h4>
     </div>
    </div>
   </div>
   <p>
    You can get a good indication of how good a join is by taking
          the product of the values in the
    <code class="literal">
     rows
    </code>
    column of the
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    output.
          This should tell you roughly how many rows MySQL must examine
          to execute the query. If you restrict queries with the
    <a class="link" href="server-system-variables.html#sysvar_max_join_size">
     <code class="literal">
      max_join_size
     </code>
    </a>
    system
          variable, this row product also is used to determine which
          multiple-table
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statements to execute and which to abort. See
    <a class="xref" href="server-configuration.html" title="7.1.1 Configuring the Server">
     Section 7.1.1, “Configuring the Server”
    </a>
    .
   </p>
   <p>
    The following example shows how a multiple-table join can be
          optimized progressively based on the information provided by
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    .
   </p>
   <p>
    Suppose that you have the
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statement shown here and
          that you plan to examine it using
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38518014"><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 keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> tt<span class="token punctuation">.</span>TicketNumber<span class="token punctuation">,</span> tt<span class="token punctuation">.</span>TimeIn<span class="token punctuation">,</span>
               tt<span class="token punctuation">.</span>ProjectReference<span class="token punctuation">,</span> tt<span class="token punctuation">.</span>EstimatedShipDate<span class="token punctuation">,</span>
               tt<span class="token punctuation">.</span>ActualShipDate<span class="token punctuation">,</span> tt<span class="token punctuation">.</span>ClientID<span class="token punctuation">,</span>
               tt<span class="token punctuation">.</span>ServiceCodes<span class="token punctuation">,</span> tt<span class="token punctuation">.</span>RepetitiveID<span class="token punctuation">,</span>
               tt<span class="token punctuation">.</span>CurrentProcess<span class="token punctuation">,</span> tt<span class="token punctuation">.</span>CurrentDPPerson<span class="token punctuation">,</span>
               tt<span class="token punctuation">.</span>RecordVolume<span class="token punctuation">,</span> tt<span class="token punctuation">.</span>DPPrinted<span class="token punctuation">,</span> et<span class="token punctuation">.</span>COUNTRY<span class="token punctuation">,</span>
               et_1<span class="token punctuation">.</span>COUNTRY<span class="token punctuation">,</span> <span class="token keyword">do</span><span class="token punctuation">.</span>CUSTNAME
        <span class="token keyword">FROM</span> tt<span class="token punctuation">,</span> et<span class="token punctuation">,</span> et <span class="token keyword">AS</span> et_1<span class="token punctuation">,</span> <span class="token keyword">do</span>
        <span class="token keyword">WHERE</span> tt<span class="token punctuation">.</span>SubmitTime <span class="token operator">IS</span> <span class="token boolean">NULL</span>
          <span class="token operator">AND</span> tt<span class="token punctuation">.</span>ActualPC <span class="token operator">=</span> et<span class="token punctuation">.</span>EMPLOYID
          <span class="token operator">AND</span> tt<span class="token punctuation">.</span>AssignedPC <span class="token operator">=</span> et_1<span class="token punctuation">.</span>EMPLOYID
          <span class="token operator">AND</span> tt<span class="token punctuation">.</span>ClientID <span class="token operator">=</span> <span class="token keyword">do</span><span class="token punctuation">.</span>CUSTNMBR<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For this example, make the following assumptions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The columns being compared have been declared as follows.
      </p>
      <div class="informaltable">
       <table summary="Table names, column names, and data types for the columns being compared in the EXPLAIN example described in the preceding text.">
        <colgroup>
         <col style="width: 10%"/>
         <col style="width: 25%"/>
         <col style="width: 25%"/>
        </colgroup>
        <thead>
         <tr>
          <th scope="col">
           Table
          </th>
          <th scope="col">
           Column
          </th>
          <th scope="col">
           Data Type
          </th>
         </tr>
        </thead>
        <tbody>
         <tr>
          <th scope="row">
           <code class="literal">
            tt
           </code>
          </th>
          <td>
           <code class="literal">
            ActualPC
           </code>
          </td>
          <td>
           <code class="literal">
            CHAR(10)
           </code>
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            tt
           </code>
          </th>
          <td>
           <code class="literal">
            AssignedPC
           </code>
          </td>
          <td>
           <code class="literal">
            CHAR(10)
           </code>
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            tt
           </code>
          </th>
          <td>
           <code class="literal">
            ClientID
           </code>
          </td>
          <td>
           <code class="literal">
            CHAR(10)
           </code>
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            et
           </code>
          </th>
          <td>
           <code class="literal">
            EMPLOYID
           </code>
          </td>
          <td>
           <code class="literal">
            CHAR(15)
           </code>
          </td>
         </tr>
         <tr>
          <th scope="row">
           <code class="literal">
            do
           </code>
          </th>
          <td>
           <code class="literal">
            CUSTNMBR
           </code>
          </td>
          <td>
           <code class="literal">
            CHAR(15)
           </code>
          </td>
         </tr>
        </tbody>
       </table>
      </div>
     </li>
     <li class="listitem">
      <p>
       The tables have the following indexes.
      </p>
      <div class="informaltable">
       <table summary="Indexes for each of the tables that are part of the EXPLAIN example described in the preceding text.">
        <colgroup>
         <col style="width: 10%"/>
         <col style="width: 40%"/>
        </colgroup>
        <thead>
         <tr>
          <th>
           Table
          </th>
          <th>
           Index
          </th>
         </tr>
        </thead>
        <tbody>
         <tr>
          <td>
           <code class="literal">
            tt
           </code>
          </td>
          <td>
           <code class="literal">
            ActualPC
           </code>
          </td>
         </tr>
         <tr>
          <td>
           <code class="literal">
            tt
           </code>
          </td>
          <td>
           <code class="literal">
            AssignedPC
           </code>
          </td>
         </tr>
         <tr>
          <td>
           <code class="literal">
            tt
           </code>
          </td>
          <td>
           <code class="literal">
            ClientID
           </code>
          </td>
         </tr>
         <tr>
          <td>
           <code class="literal">
            et
           </code>
          </td>
          <td>
           <code class="literal">
            EMPLOYID
           </code>
           (primary key)
          </td>
         </tr>
         <tr>
          <td>
           <code class="literal">
            do
           </code>
          </td>
          <td>
           <code class="literal">
            CUSTNMBR
           </code>
           (primary key)
          </td>
         </tr>
        </tbody>
       </table>
      </div>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        tt.ActualPC
       </code>
       values are not evenly
              distributed.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Initially, before any optimizations have been performed, the
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    statement produces the
          following information:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa70481847"><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-none">table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)</code></pre>
   </div>
   <p>
    Because
    <code class="literal">
     type
    </code>
    is
    <a class="link" href="explain-output.html#jointype_all">
     <code class="literal">
      ALL
     </code>
    </a>
    for each table, this
          output indicates that MySQL is generating a Cartesian product
          of all the tables; that is, every combination of rows. This
          takes quite a long time, because the product of the number of
          rows in each table must be examined. For the case at hand,
          this product is 74 × 2135 × 74 × 3872 =
          45,268,558,720 rows. If the tables were bigger, you can only
          imagine how long it would take.
   </p>
   <p>
    One problem here is that MySQL can use indexes on columns more
          efficiently if they are declared as the same type and size. In
          this context,
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      VARCHAR
     </code>
    </a>
    and
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      CHAR
     </code>
    </a>
    are considered the same if
          they are declared as the same size.
    <code class="literal">
     tt.ActualPC
    </code>
    is declared as
    <code class="literal">
     CHAR(10)
    </code>
    and
    <code class="literal">
     et.EMPLOYID
    </code>
    is
    <code class="literal">
     CHAR(15)
    </code>
    , so there is a length mismatch.
   </p>
   <p>
    To fix this disparity between column lengths, use
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    to lengthen
    <code class="literal">
     ActualPC
    </code>
    from 10 characters to 15
          characters:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47696146"><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">ALTER</span> <span class="token keyword">TABLE</span> tt <span class="token keyword">MODIFY</span> ActualPC <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Now
    <code class="literal">
     tt.ActualPC
    </code>
    and
    <code class="literal">
     et.EMPLOYID
    </code>
    are both
    <code class="literal">
     VARCHAR(15)
    </code>
    . Executing the
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    statement again
          produces this result:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa59432535"><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-none">table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1</code></pre>
   </div>
   <p>
    This is not perfect, but is much better: The product of the
    <code class="literal">
     rows
    </code>
    values is less by a factor of 74. This
          version executes in a couple of seconds.
   </p>
   <p>
    A second alteration can be made to eliminate the column length
          mismatches for the
    <code class="literal">
     tt.AssignedPC =
          et_1.EMPLOYID
    </code>
    and
    <code class="literal">
     tt.ClientID =
          do.CUSTNMBR
    </code>
    comparisons:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10146284"><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">ALTER</span> <span class="token keyword">TABLE</span> tt <span class="token keyword">MODIFY</span> AssignedPC <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
                      <span class="token keyword">MODIFY</span> ClientID   <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    After that modification,
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    produces the output
          shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa39975523"><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-none">table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1</code></pre>
   </div>
   <p>
    At this point, the query is optimized almost as well as
          possible. The remaining problem is that, by default, MySQL
          assumes that values in the
    <code class="literal">
     tt.ActualPC
    </code>
    column are evenly distributed, and that is not the case for
          the
    <code class="literal">
     tt
    </code>
    table. Fortunately, it is easy to
          tell MySQL to analyze the key distribution:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa29303613"><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">ANALYZE</span> <span class="token keyword">TABLE</span> tt<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    With the additional index information, the join is perfect and
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    produces this result:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-none"><div class="docs-select-all right" id="sa27683717"><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-none">table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1</code></pre>
   </div>
   <p>
    <a class="indexterm" name="idm46045224698032">
    </a>
    The
    <code class="literal">
     rows
    </code>
    column in the output from
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    is an educated guess
          from the MySQL join optimizer. Check whether the numbers are
          even close to the truth by comparing the
    <code class="literal">
     rows
    </code>
    product with the actual number of rows
          that the query returns. If the numbers are quite different,
          you might get better performance by using
    <code class="literal">
     STRAIGHT_JOIN
    </code>
    in your
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statement and trying to
          list the tables in a different order in the
    <code class="literal">
     FROM
    </code>
    clause. (However,
    <code class="literal">
     STRAIGHT_JOIN
    </code>
    may prevent indexes from
          being used because it disables semijoin transformations. 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>
   <p>
    It is possible in some cases to execute statements that modify
          data when
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
          SELECT
     </code>
    </a>
    is used with a subquery; for more
          information, see
    <a class="xref" href="derived-tables.html" title="15.2.15.8 Derived Tables">
     Section 15.2.15.8, “Derived Tables”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
