<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="subquery-optimization">
      </a>
      10.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table
Expressions
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="semijoins-antijoins.html">
       10.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin
          Transformations
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="subquery-materialization.html">
       10.2.2.2 Optimizing Subqueries with Materialization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="subquery-optimization-with-exists.html">
       10.2.2.3 Optimizing Subqueries with the EXISTS Strategy
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="derived-table-optimization.html">
       10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions
with Merging or Materialization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="derived-condition-pushdown-optimization.html">
       10.2.2.5 Derived Condition Pushdown Optimization
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045228413680">
  </a>
  <a class="indexterm" name="idm46045228412224">
  </a>
  <a class="indexterm" name="idm46045228410736">
  </a>
  <a class="indexterm" name="idm46045228409248">
  </a>
  <a class="indexterm" name="idm46045228407760">
  </a>
  <a class="indexterm" name="idm46045228406272">
  </a>
  <a class="indexterm" name="idm46045228404784">
  </a>
  <a class="indexterm" name="idm46045228403280">
  </a>
  <p>
   The MySQL query optimizer has different strategies available to
        evaluate subqueries:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For a subquery used with an
      <code class="literal">
       IN
      </code>
      ,
      <code class="literal">
       = ANY
      </code>
      , or
      <code class="literal">
       EXISTS
      </code>
      predicate, the optimizer has these choices:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Semijoin
        </p>
       </li>
       <li class="listitem">
        <p>
         Materialization
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          EXISTS
         </code>
         strategy
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      For a subquery used with a
      <code class="literal">
       NOT IN
      </code>
      ,
      <code class="literal">
       &lt;&gt; ALL
      </code>
      or
      <code class="literal">
       NOT
            EXISTS
      </code>
      predicate, the optimizer has these choices:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Materialization
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          EXISTS
         </code>
         strategy
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   For a derived table, the optimizer has these choices (which also
        apply to view references and common table expressions):
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Merge the derived table into the outer query block
     </p>
    </li>
    <li class="listitem">
     <p>
      Materialize the derived table to an internal temporary table
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following discussion provides more information about the
        preceding optimization strategies.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    A limitation on
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    and
    <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
     <code class="literal">
      DELETE
     </code>
    </a>
    statements that use a
          subquery to modify a single table is that the optimizer does
          not use semijoin or materialization subquery optimizations. As
          a workaround, try rewriting them as multiple-table
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    and
    <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
     <code class="literal">
      DELETE
     </code>
    </a>
    statements that use a
          join rather than a subquery.
   </p>
  </div>
 </div>
 <br/>
</div>
