<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="using-explain">
      </a>
      10.8.1 Optimizing Queries with EXPLAIN
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045225610896">
  </a>
  <a class="indexterm" name="idm46045225609856">
  </a>
  <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:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <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>
    </li>
    <li class="listitem">
     <p>
      When
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      is used with an
            explainable statement, MySQL displays information from the
            optimizer about the statement execution plan. That is, MySQL
            explains how it would process the statement, including
            information about how tables are joined and in which order.
            For information about using
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      to obtain execution
            plan information, see
      <a class="xref" href="explain-output.html" title="10.8.2 EXPLAIN Output Format">
       Section 10.8.2, “EXPLAIN Output Format”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      is used with
      <code class="literal">
       FOR CONNECTION
       <em class="replaceable">
        <code>
         connection_id
        </code>
       </em>
      </code>
      rather
            than an explainable statement, it displays the execution
            plan for the statement executing in the named connection.
            See
      <a class="xref" href="explain-for-connection.html" title="10.8.4 Obtaining Execution Plan Information for a Named Connection">
       Section 10.8.4, “Obtaining Execution Plan Information for a Named Connection”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements,
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      produces additional
            execution plan information that can be displayed using
      <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
       <code class="literal">
        SHOW WARNINGS
       </code>
      </a>
      . See
      <a class="xref" href="explain-extended.html" title="10.8.3 Extended EXPLAIN Output Format">
       Section 10.8.3, “Extended EXPLAIN Output Format”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      is useful for
            examining queries involving partitioned 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>
      The
      <code class="literal">
       FORMAT
      </code>
      option can be used to select
            the output format.
      <code class="literal">
       TRADITIONAL
      </code>
      presents
            the output in tabular format. This is the default if no
      <code class="literal">
       FORMAT
      </code>
      option is present.
      <code class="literal">
       JSON
      </code>
      format displays the information in
            JSON format.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a class="indexterm" name="idm46045225576624">
   </a>
   With the help of
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   , you can
        see where you should add indexes to tables so that the statement
        executes faster by using indexes to find rows. You can also use
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   to check whether the
        optimizer joins the tables in an optimal order. To give a hint
        to the optimizer to use a join order corresponding to the order
        in which the tables are named in a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement, begin the
        statement with
   <code class="literal">
    SELECT STRAIGHT_JOIN
   </code>
   rather
        than just
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   . (See
   <a class="xref" href="select.html" title="15.2.13 SELECT Statement">
    Section 15.2.13, “SELECT Statement”
   </a>
   .) 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>
   The optimizer trace may sometimes provide information
        complementary to that of
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   .
        However, the optimizer trace format and content are subject to
        change between versions. For details, see
   <a class="xref" href="optimizer-tracing.html" title="10.15 Tracing the Optimizer">
    Section 10.15, “Tracing the Optimizer”
   </a>
   .
  </p>
  <p>
   If you have a problem with indexes not being used when you
        believe that they should be, run
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE
        TABLE
    </code>
   </a>
   to update table statistics, such as cardinality
        of keys, that can affect the choices the optimizer makes. 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>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    can also be used to
          obtain information about the columns in a table.
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
     </code>
    </a>
    is synonymous
          with
    <code class="literal">
     DESCRIBE
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
    </code>
    and
    <code class="literal">
     SHOW COLUMNS FROM
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
    </code>
    . For more
          information, see
    <a class="xref" href="describe.html" title="15.8.1 DESCRIBE Statement">
     Section 15.8.1, “DESCRIBE Statement”
    </a>
    , and
    <a class="xref" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
     Section 15.7.7.6, “SHOW COLUMNS Statement”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
