<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="derived-table-optimization">
      </a>
      10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions
with Merging or Materialization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227970896">
  </a>
  <a class="indexterm" name="idm46045227969408">
  </a>
  <a class="indexterm" name="idm46045227967920">
  </a>
  <a class="indexterm" name="idm46045227966464">
  </a>
  <a class="indexterm" name="idm46045227964976">
  </a>
  <a class="indexterm" name="idm46045227963488">
  </a>
  <a class="indexterm" name="idm46045227961984">
  </a>
  <a class="indexterm" name="idm46045227960496">
  </a>
  <a class="indexterm" name="idm46045227959008">
  </a>
  <p>
   The optimizer can handle derived table references using two
          strategies (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>
   Example 1:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38094429"><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> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span> <span class="token keyword">AS</span> derived_t1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   With merging of the derived table
   <code class="literal">
    derived_t1
   </code>
   , that query is executed similar
          to:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa89644531"><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 punctuation">;</span></code></pre>
  </div>
  <p>
   Example 2:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30784521"><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">JOIN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>f1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">AS</span> derived_t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>f2<span class="token operator">=</span>derived_t2<span class="token punctuation">.</span>f1
  <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>f1 <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   With merging of the derived table
   <code class="literal">
    derived_t2
   </code>
   , that query is executed similar
          to:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99484162"><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> t1<span class="token punctuation">.</span><span class="token operator">*</span><span class="token punctuation">,</span> t2<span class="token punctuation">.</span>f1
  <span class="token keyword">FROM</span> t1 <span class="token keyword">JOIN</span> t2 <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>f2<span class="token operator">=</span>t2<span class="token punctuation">.</span>f1
  <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>f1 <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   With materialization,
   <code class="literal">
    derived_t1
   </code>
   and
   <code class="literal">
    derived_t2
   </code>
   are each treated as a separate
          table within their respective queries.
  </p>
  <p>
   The optimizer handles derived tables, view references, and
          common table expressions the same way: It avoids unnecessary
          materialization whenever possible, which enables pushing down
          conditions from the outer query to derived tables and produces
          more efficient execution plans. (For an example, 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>
  <p>
   If merging would result in an outer query block that
          references more than 61 base tables, the optimizer chooses
          materialization instead.
  </p>
  <p>
   The optimizer propagates an
   <code class="literal">
    ORDER BY
   </code>
   clause
          in a derived table or view reference to the outer query block
          if these conditions are all true:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The outer query is not grouped or aggregated.
     </p>
    </li>
    <li class="listitem">
     <p>
      The outer query does not specify
      <code class="literal">
       DISTINCT
      </code>
      ,
      <code class="literal">
       HAVING
      </code>
      , or
      <code class="literal">
       ORDER BY
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The outer query has this derived table or view reference
              as the only source in the
      <code class="literal">
       FROM
      </code>
      clause.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Otherwise, the optimizer ignores the
   <code class="literal">
    ORDER
          BY
   </code>
   clause.
  </p>
  <p>
   The following means are available to influence whether the
          optimizer attempts to merge derived tables, view references,
          and common table expressions into the outer query block:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
       <code class="literal">
        MERGE
       </code>
      </a>
      and
      <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
       <code class="literal">
        NO_MERGE
       </code>
      </a>
      optimizer hints
              can be used. They apply assuming that no other rule
              prevents merging. See
      <a class="xref" href="optimizer-hints.html" title="10.9.3 Optimizer Hints">
       Section 10.9.3, “Optimizer Hints”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Similarly, you can use the
      <a class="link" href="switchable-optimizations.html#optflag_derived-merge">
       <code class="literal">
        derived_merge
       </code>
      </a>
      flag of
              the
      <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
       <code class="literal">
        optimizer_switch
       </code>
      </a>
      system variable. See
      <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
       Section 10.9.2, “Switchable Optimizations”
      </a>
      . By default,
              the flag is enabled to permit merging. Disabling the flag
              prevents merging and avoids
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_update_table_used" target="_top">
       <code class="literal">
        ER_UPDATE_TABLE_USED
       </code>
      </a>
      errors.
     </p>
     <p>
      The
      <a class="link" href="switchable-optimizations.html#optflag_derived-merge">
       <code class="literal">
        derived_merge
       </code>
      </a>
      flag
              also applies to views that contain no
      <code class="literal">
       ALGORITHM
      </code>
      clause. Thus, if an
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_update_table_used" target="_top">
       <code class="literal">
        ER_UPDATE_TABLE_USED
       </code>
      </a>
      error
              occurs for a view reference that uses an expression
              equivalent to the subquery, adding
      <code class="literal">
       ALGORITHM=TEMPTABLE
      </code>
      to the view
              definition prevents merging and takes precedence over the
      <a class="link" href="switchable-optimizations.html#optflag_derived-merge">
       <code class="literal">
        derived_merge
       </code>
      </a>
      value.
     </p>
    </li>
    <li class="listitem">
     <p>
      It is possible to disable merging by using in the subquery
              any constructs that prevent merging, although these are
              not as explicit in their effect on materialization.
              Constructs that prevent merging are the same for derived
              tables, common table expressions, and view references:
     </p>
     <div class="itemizedlist">
      <a class="indexterm" name="idm46045227917648">
      </a>
      <a class="indexterm" name="idm46045227916144">
      </a>
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Aggregate functions or window functions
                  (
         <a class="link" href="aggregate-functions.html#function_sum">
          <code class="literal">
           SUM()
          </code>
         </a>
         ,
         <a class="link" href="aggregate-functions.html#function_min">
          <code class="literal">
           MIN()
          </code>
         </a>
         ,
         <a class="link" href="aggregate-functions.html#function_max">
          <code class="literal">
           MAX()
          </code>
         </a>
         ,
         <a class="link" href="aggregate-functions.html#function_count">
          <code class="literal">
           COUNT()
          </code>
         </a>
         , and so forth)
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          DISTINCT
         </code>
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          GROUP BY
         </code>
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          HAVING
         </code>
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          LIMIT
         </code>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="union.html" title="15.2.18 UNION Clause">
          <code class="literal">
           UNION
          </code>
         </a>
         or
         <a class="link" href="union.html" title="15.2.18 UNION Clause">
          <code class="literal">
           UNION
                  ALL
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         Subqueries in the select list
        </p>
       </li>
       <li class="listitem">
        <p>
         Assignments to user variables
        </p>
       </li>
       <li class="listitem">
        <p>
         References only to literal values (in this case, there
                  is no underlying table)
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   If the optimizer chooses the materialization strategy rather
          than merging for a derived table, it handles the query as
          follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The optimizer postpones derived table materialization
              until its contents are needed during query execution. This
              improves performance because delaying materialization may
              result in not having to do it at all. Consider a query
              that joins the result of a derived table to another table:
              If the optimizer processes that other table first and
              finds that it returns no rows, the join need not be
              carried out further and the optimizer can completely skip
              materializing the derived table.
     </p>
    </li>
    <li class="listitem">
     <p>
      During query execution, the optimizer may add an index to
              a derived table to speed up row retrieval from it.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Consider the following
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   statement, for a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   query
          that contains a derived table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91662388"><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> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span> <span class="token keyword">AS</span> derived_t1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The optimizer avoids materializing the derived table by
          delaying it until the result is needed during
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   execution. In this case,
          the query is not executed (because it occurs in an
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   statement), so the
          result is never needed.
  </p>
  <p>
   Even for queries that are executed, delay of derived table
          materialization may enable the optimizer to avoid
          materialization entirely. When this happens, query execution
          is quicker by the time needed to perform materialization.
          Consider the following query, which joins the result of a
          derived table to another table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20781638"><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">JOIN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>f1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">AS</span> derived_t2
          <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>f2<span class="token operator">=</span>derived_t2<span class="token punctuation">.</span>f1
  <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>f1 <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If the optimization processes
   <code class="literal">
    t1
   </code>
   first and
          the
   <code class="literal">
    WHERE
   </code>
   clause produces an empty result,
          the join must necessarily be empty and the derived table need
          not be materialized.
  </p>
  <p>
   For cases when a derived table requires materialization, the
          optimizer may add an index to the materialized table to speed
          up access to it. If such an index enables
   <a class="link" href="explain-output.html#jointype_ref">
    <code class="literal">
     ref
    </code>
   </a>
   access to the table, it
          can greatly reduce amount of data read during query execution.
          Consider the following query:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa91634288"><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">JOIN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> f1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">AS</span> derived_t2
         <span class="token keyword">ON</span> t1<span class="token punctuation">.</span>f1<span class="token operator">=</span>derived_t2<span class="token punctuation">.</span>f1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The optimizer constructs an index over column
   <code class="literal">
    f1
   </code>
   from
   <code class="literal">
    derived_t2
   </code>
   if
          doing so would enable use of
   <a class="link" href="explain-output.html#jointype_ref">
    <code class="literal">
     ref
    </code>
   </a>
   access for the lowest
          cost execution plan. After adding the index, the optimizer can
          treat the materialized derived table the same as a regular
          table with an index, and it benefits similarly from the
          generated index. The overhead of index creation is negligible
          compared to the cost of query execution without the index. If
   <a class="link" href="explain-output.html#jointype_ref">
    <code class="literal">
     ref
    </code>
   </a>
   access would result in
          higher cost than some other access method, the optimizer
          creates no index and loses nothing.
  </p>
  <p>
   For optimizer trace output, a merged derived table or view
          reference is not shown as a node. Only its underlying tables
          appear in the top query's plan.
  </p>
  <p>
   What is true for materialization of derived tables is also
          true for common table expressions (CTEs). In addition, the
          following considerations pertain specifically to CTEs.
  </p>
  <p>
   If a CTE is materialized by a query, it is materialized once
          for the query, even if the query references it several times.
  </p>
  <p>
   A recursive CTE is always materialized.
  </p>
  <p>
   If a CTE is materialized, the optimizer automatically adds
          relevant indexes if it estimates that indexing can speed up
          access by the top-level statement to the CTE. This is similar
          to automatic indexing of derived tables, except that if the
          CTE is referenced multiple times, the optimizer may create
          multiple indexes, to speed up access by each reference in the
          most appropriate way.
  </p>
  <p>
   The
   <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
    <code class="literal">
     MERGE
    </code>
   </a>
   and
   <a class="link" href="optimizer-hints.html#optimizer-hints-table-level" title="Table-Level Optimizer Hints">
    <code class="literal">
     NO_MERGE
    </code>
   </a>
   optimizer hints can
          be applied to CTEs. Each CTE reference in the top-level
          statement can have its own hint, permitting CTE references to
          be selectively merged or materialized. The following statement
          uses hints to indicate that
   <code class="literal">
    cte1
   </code>
   should be
          merged and
   <code class="literal">
    cte2
   </code>
   should be materialized:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72530084"><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">WITH</span>
  cte1 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> a<span class="token punctuation">,</span> b <span class="token keyword">FROM</span> table1<span class="token punctuation">)</span><span class="token punctuation">,</span>
  cte2 <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> c<span class="token punctuation">,</span> d <span class="token keyword">FROM</span> table2<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token comment" spellcheck="true">/*+ MERGE(cte1) NO_MERGE(cte2) */</span> cte1<span class="token punctuation">.</span>b<span class="token punctuation">,</span> cte2<span class="token punctuation">.</span>d
<span class="token keyword">FROM</span> cte1 <span class="token keyword">JOIN</span> cte2
<span class="token keyword">WHERE</span> cte1<span class="token punctuation">.</span>a <span class="token operator">=</span> cte2<span class="token punctuation">.</span>c<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    ALGORITHM
   </code>
   clause for
   <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
    <code class="literal">
     CREATE VIEW
    </code>
   </a>
   does not affect
          materialization for any
   <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
    <code class="literal">
     WITH
    </code>
   </a>
   clause preceding the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement in the view definition. Consider this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa44208767"><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">CREATE</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>{<span class="token keyword">TEMPTABLE</span><span class="token operator">|</span><span class="token keyword">MERGE</span>} <span class="token keyword">VIEW</span> v1 <span class="token keyword">AS</span> <span class="token keyword">WITH</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    ALGORITHM
   </code>
   value affects materialization
          only of the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   , not the
   <a class="link" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
    <code class="literal">
     WITH
    </code>
   </a>
   clause.
  </p>
  <p>
   As mentioned previously, a CTE, if materialized, is
          materialized once, even if referenced multiple times. To
          indicate one-time materialization, optimizer trace output
          contains an occurrence of
   <code class="literal">
    creating_tmp_table
   </code>
   plus one or more
          occurrences of
   <code class="literal">
    reusing_tmp_table
   </code>
   .
  </p>
  <p>
   CTEs are similar to derived tables, for which the
   <code class="literal">
    materialized_from_subquery
   </code>
   node follows the
          reference. This is true for a CTE that is referenced multiple
          times, so there is no duplication of
   <code class="literal">
    materialized_from_subquery
   </code>
   nodes (which
          would give the impression that the subquery is executed
          multiple times, and produce unnecessarily verbose output).
          Only one reference to the CTE has a complete
   <code class="literal">
    materialized_from_subquery
   </code>
   node with the
          description of its subquery plan. Other references have a
          reduced
   <code class="literal">
    materialized_from_subquery
   </code>
   node.
          The same idea applies to
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output in
   <code class="literal">
    TRADITIONAL
   </code>
   format: Subqueries for other
          references are not shown.
  </p>
 </div>
 <br/>
</div>
