<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="derived-condition-pushdown-optimization">
      </a>
      10.2.2.5 Derived Condition Pushdown Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227846928">
  </a>
  <a class="indexterm" name="idm46045227845840">
  </a>
  <p>
   MySQL supports derived condition pushdown for eligible
          subqueries. For a query such as
   <code class="literal">
    SELECT * FROM (SELECT
          i, j FROM t1) AS dt WHERE i &gt;
    <em class="replaceable">
     <code>
      constant
     </code>
    </em>
   </code>
   , it is possible
          in many cases to push the outer
   <code class="literal">
    WHERE
   </code>
   condition down to the derived table, in this case resulting in
   <code class="literal">
    SELECT * FROM (SELECT i, j FROM t1 WHERE i &gt;
    <em class="replaceable">
     <code>
      constant
     </code>
    </em>
    ) AS dt
   </code>
   . When a
          derived table cannot be merged into the outer query (for
          example, if the derived table uses aggregation), pushing the
          outer
   <code class="literal">
    WHERE
   </code>
   condition down to the derived
          table should decrease the number of rows that need to be
          processed and thus speed up execution of the query.
  </p>
  <p>
   Outer
   <code class="literal">
    WHERE
   </code>
   conditions can be pushed down
          to derived materialized tables under the following
          circumstances:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When the derived table uses no aggregate or window
              functions, the outer
      <code class="literal">
       WHERE
      </code>
      condition
              can be pushed down to it directly. This includes
      <code class="literal">
       WHERE
      </code>
      conditions having multiple
              predicates joined with
      <code class="literal">
       AND
      </code>
      ,
      <code class="literal">
       OR
      </code>
      , or both.
     </p>
     <p>
      For example, the query
      <code class="literal">
       SELECT * FROM (SELECT f1,
              f2 FROM t1) AS dt WHERE f1 &lt; 3 AND f2 &gt; 11
      </code>
      is rewritten as
      <code class="literal">
       SELECT f1, f2 FROM (SELECT f1, f2
              FROM t1 WHERE f1 &lt; 3 AND f2 &gt; 11) AS dt
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When the derived table has a
      <code class="literal">
       GROUP BY
      </code>
      and uses no window functions, an outer
      <code class="literal">
       WHERE
      </code>
      condition referencing one or more
              columns which are not part of the
      <code class="literal">
       GROUP
              BY
      </code>
      can be pushed down to the derived table as a
      <code class="literal">
       HAVING
      </code>
      condition.
     </p>
     <p>
      For example,
      <code class="literal">
       SELECT * FROM (SELECT i, j, SUM(k)
              AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum &gt;
              100
      </code>
      is rewritten following derived condition
              pushdown as
      <code class="literal">
       SELECT * FROM (SELECT i, j, SUM(k) AS
              sum FROM t1 GROUP BY i, j HAVING sum &gt; 100) AS
              dt
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When the derived table uses a
      <code class="literal">
       GROUP BY
      </code>
      and the columns in the outer
      <code class="literal">
       WHERE
      </code>
      condition are
      <code class="literal">
       GROUP BY
      </code>
      columns, the
      <code class="literal">
       WHERE
      </code>
      conditions referencing those
              columns can be pushed down directly to the derived table.
     </p>
     <p>
      For example, the query
      <code class="literal">
       SELECT * FROM (SELECT i,j,
              SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i &gt;
              10
      </code>
      is rewritten as
      <code class="literal">
       SELECT * FROM
              (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i &gt; 10 GROUP
              BY i,j) AS dt
      </code>
      .
     </p>
     <p>
      In the event that the outer
      <code class="literal">
       WHERE
      </code>
      condition has predicates referencing columns which are
              part of the
      <code class="literal">
       GROUP BY
      </code>
      as well as
              predicates referencing columns which are not, predicates
              of the former sort are pushed down as
      <code class="literal">
       WHERE
      </code>
      conditions, while those of the
              latter type are pushed down as
      <code class="literal">
       HAVING
      </code>
      conditions. For example, in the query
      <code class="literal">
       SELECT *
              FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS
              dt WHERE i &gt; 10 AND sum &gt; 100
      </code>
      , the
              predicate
      <code class="literal">
       i &gt; 10
      </code>
      in the outer
      <code class="literal">
       WHERE
      </code>
      clause references a
      <code class="literal">
       GROUP BY
      </code>
      column, whereas the predicate
      <code class="literal">
       sum &gt; 100
      </code>
      does not reference any
      <code class="literal">
       GROUP BY
      </code>
      column. Thus the derived table
              pushdown optimization causes the query to be rewritten in
              a manner similar to what is shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa94714223"><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> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> i<span class="token punctuation">,</span> j<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>k<span class="token punctuation">)</span> <span class="token keyword">AS</span> sum <span class="token keyword">FROM</span> t1
        <span class="token keyword">WHERE</span> i <span class="token operator">&gt;</span> <span class="token number">10</span>
        <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> i<span class="token punctuation">,</span> j
        <span class="token keyword">HAVING</span> sum <span class="token operator">&gt;</span> <span class="token number">100</span>
    <span class="token punctuation">)</span> <span class="token keyword">AS</span> dt<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   To enable derived condition pushdown, the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system
          variable's
   <a class="link" href="switchable-optimizations.html#optflag_derived-condition-pushdown">
    <code class="literal">
     derived_condition_pushdown
    </code>
   </a>
   flag (added in this release) must be set to
   <code class="literal">
    on
   </code>
   , which is the default setting. If this
          optimization is disabled by
   <code class="literal">
    optimizer_switch
   </code>
   , you can enable it for a
          specific query using the
   <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
    <code class="literal">
     DERIVED_CONDITION_PUSHDOWN
    </code>
   </a>
   optimizer hint. To disable the optimization for a given query,
          use the
   <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
    <code class="literal">
     NO_DERIVED_CONDITION_PUSHDOWN
    </code>
   </a>
   optimizer hint.
  </p>
  <p>
   The following restrictions and limitations apply to the
          derived table condition pushdown optimization:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The derived table condition pushdown optimization can be
              employed with
      <code class="literal">
       UNION
      </code>
      queries, with the
              following exceptions:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Condition pushdown cannot be used with a
         <code class="literal">
          UNION
         </code>
         query if any materialized
                  derived table that is part of the
         <code class="literal">
          UNION
         </code>
         is a recursive common table
                  expression (see
         <a class="xref" href="with.html#common-table-expressions-recursive" title="Recursive Common Table Expressions">
          Recursive Common Table Expressions
         </a>
         ).
        </p>
       </li>
       <li class="listitem">
        <p>
         Conditions containing nondeterministic expressions
                  cannot be pushed down to a derived table.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      The derived table cannot use a
      <code class="literal">
       LIMIT
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      Conditions containing subqueries cannot be pushed down.
     </p>
    </li>
    <li class="listitem">
     <p>
      The optimization cannot be used if the derived table is an
              inner table of an outer join.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a materialized derived table is a common table
              expression, conditions are not pushed down to it if it is
              referenced multiple times.
     </p>
    </li>
    <li class="listitem">
     <p>
      Conditions using parameters can be pushed down if the
              condition is of the form
      <code class="literal">
       <em class="replaceable">
        <code>
         derived_column
        </code>
       </em>
       &gt;
              ?
      </code>
      . If a derived column in an outer
      <code class="literal">
       WHERE
      </code>
      condition is an expression having
              a
      <code class="literal">
       ?
      </code>
      in the underlying derived table,
              this condition cannot be pushed down.
     </p>
    </li>
    <li class="listitem">
     <p>
      For a query in which the condition is on the tables of a
              view created using
      <code class="literal">
       ALGORITHM=TEMPTABLE
      </code>
      instead of on the view itself, the multiple equality is
              not recognized at resolution, and thus the condition
              cannot be not pushed down. This because, when optimizing a
              query, condition pushdown takes place during resolution
              phase while multiple equality propagation occurs during
              optimization.
     </p>
     <p>
      This is not an issue in such cases for a view using
      <code class="literal">
       ALGORITHM=MERGE
      </code>
      , where the equality can
              be propagated and the condition pushed down.
     </p>
    </li>
    <li class="listitem">
     <p>
      A condition cannot be pushed down if the derived
              table's
      <code class="literal">
       SELECT
      </code>
      list contain any
              assignments to user variables.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
