<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="subquery-materialization">
      </a>
      10.2.2.2 Optimizing Subqueries with Materialization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228220464">
  </a>
  <a class="indexterm" name="idm46045228219424">
  </a>
  <a class="indexterm" name="idm46045228217936">
  </a>
  <p>
   The optimizer uses materialization to enable more efficient
          subquery processing. Materialization speeds up query execution
          by generating a subquery result as a temporary table, normally
          in memory. The first time MySQL needs the subquery result, it
          materializes that result into a temporary table. Any
          subsequent time the result is needed, MySQL refers again to
          the temporary table. The optimizer may index the table with a
          hash index to make lookups fast and inexpensive. The index
          contains unique values to eliminate duplicates and make the
          table smaller.
  </p>
  <p>
   Subquery materialization uses an in-memory temporary table
          when possible, falling back to on-disk storage if the table
          becomes too large. See
   <a class="xref" href="internal-temporary-tables.html" title="10.4.4 Internal Temporary Table Use in MySQL">
    Section 10.4.4, “Internal Temporary Table Use in MySQL”
   </a>
   .
  </p>
  <p>
   If materialization is not used, the optimizer sometimes
          rewrites a noncorrelated subquery as a correlated subquery.
          For example, the following
   <code class="literal">
    IN
   </code>
   subquery is
          noncorrelated (
   <em class="replaceable">
    <code>
     where_condition
    </code>
   </em>
   involves only columns from
   <code class="literal">
    t2
   </code>
   and not
   <code class="literal">
    t1
   </code>
   ):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4243619"><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">WHERE</span> t1<span class="token punctuation">.</span>a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>b <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> <em class="replaceable">where_condition</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The optimizer might rewrite this as an
   <code class="literal">
    EXISTS
   </code>
   correlated subquery:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33649410"><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">WHERE</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>b <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> <em class="replaceable">where_condition</em> <span class="token operator">AND</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>b<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Subquery materialization using a temporary table avoids such
          rewrites and makes it possible to execute the subquery only
          once rather than once per row of the outer query.
  </p>
  <p>
   For subquery materialization to be used in MySQL, the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system
          variable
   <a class="link" href="switchable-optimizations.html#optflag_materialization">
    <code class="literal">
     materialization
    </code>
   </a>
   flag must be enabled. (See
   <a class="xref" href="switchable-optimizations.html" title="10.9.2 Switchable Optimizations">
    Section 10.9.2, “Switchable Optimizations”
   </a>
   .) With the
   <a class="link" href="switchable-optimizations.html#optflag_materialization">
    <code class="literal">
     materialization
    </code>
   </a>
   flag
          enabled, materialization applies to subquery predicates that
          appear anywhere (in the select list,
   <code class="literal">
    WHERE
   </code>
   ,
   <code class="literal">
    ON
   </code>
   ,
   <code class="literal">
    GROUP BY
   </code>
   ,
   <code class="literal">
    HAVING
   </code>
   , or
   <code class="literal">
    ORDER BY
   </code>
   ),
          for predicates that fall into any of these use cases:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The predicate has this form, when no outer expression
      <em class="replaceable">
       <code>
        oe_i
       </code>
      </em>
      or inner expression
      <em class="replaceable">
       <code>
        ie_i
       </code>
      </em>
      is nullable.
      <em class="replaceable">
       <code>
        N
       </code>
      </em>
      is 1 or larger.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91268358"><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 punctuation">(</span><em class="replaceable">oe_1</em><span class="token punctuation">,</span> <em class="replaceable">oe_2</em><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> <em class="replaceable">oe_N</em><span class="token punctuation">)</span> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">ie_1</em><span class="token punctuation">,</span> <em class="replaceable">i_2</em><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> <em class="replaceable">ie_N</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The predicate has this form, when there is a single outer
              expression
      <em class="replaceable">
       <code>
        oe
       </code>
      </em>
      and inner
              expression
      <em class="replaceable">
       <code>
        ie
       </code>
      </em>
      . The expressions
              can be nullable.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa46470425"><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"><em class="replaceable">oe</em> <span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <em class="replaceable">ie</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      The predicate is
      <code class="literal">
       IN
      </code>
      or
      <code class="literal">
       NOT
              IN
      </code>
      and a result of
      <code class="literal">
       UNKNOWN
      </code>
      (
      <code class="literal">
       NULL
      </code>
      ) has the same meaning as a result
              of
      <code class="literal">
       FALSE
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following examples illustrate how the requirement for
          equivalence of
   <code class="literal">
    UNKNOWN
   </code>
   and
   <code class="literal">
    FALSE
   </code>
   predicate evaluation affects whether
          subquery materialization can be used. Assume that
   <em class="replaceable">
    <code>
     where_condition
    </code>
   </em>
   involves columns
          only from
   <code class="literal">
    t2
   </code>
   and not
   <code class="literal">
    t1
   </code>
   so that the subquery is noncorrelated.
  </p>
  <p>
   This query is subject to materialization:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44275379"><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">WHERE</span> t1<span class="token punctuation">.</span>a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>b <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> <em class="replaceable">where_condition</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Here, it does not matter whether the
   <code class="literal">
    IN
   </code>
   predicate returns
   <code class="literal">
    UNKNOWN
   </code>
   or
   <code class="literal">
    FALSE
   </code>
   . Either way, the row from
   <code class="literal">
    t1
   </code>
   is not included in the query result.
  </p>
  <p>
   An example where subquery materialization is not used is the
          following query, where
   <code class="literal">
    t2.b
   </code>
   is a nullable
          column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa37034004"><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">WHERE</span> <span class="token punctuation">(</span>t1<span class="token punctuation">.</span>a<span class="token punctuation">,</span>t1<span class="token punctuation">.</span>b<span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> t2<span class="token punctuation">.</span>a<span class="token punctuation">,</span>t2<span class="token punctuation">.</span>b <span class="token keyword">FROM</span> t2
                          <span class="token keyword">WHERE</span> <em class="replaceable">where_condition</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The following restrictions apply to the use of subquery
          materialization:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The types of the inner and outer expressions must match.
              For example, the optimizer might be able to use
              materialization if both expressions are integer or both
              are decimal, but cannot if one expression is integer and
              the other is decimal.
     </p>
    </li>
    <li class="listitem">
     <p>
      The inner expression cannot be a
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Use of
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   with a query
          provides some indication of whether the optimizer uses
          subquery materialization:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Compared to query execution that does not use
              materialization,
      <code class="literal">
       select_type
      </code>
      may change
              from
      <code class="literal">
       DEPENDENT SUBQUERY
      </code>
      to
      <code class="literal">
       SUBQUERY
      </code>
      . This indicates that, for a
              subquery that would be executed once per outer row,
              materialization enables the subquery to be executed just
              once.
     </p>
    </li>
    <li class="listitem">
     <p>
      For extended
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      output, the text displayed by a following
      <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
       <code class="literal">
        SHOW WARNINGS
       </code>
      </a>
      includes
      <code class="literal">
       materialize
      </code>
      and
      <code class="literal">
       materialized-subquery
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   MySQL can also apply subquery materialization to a
          single-table
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   or
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statement that uses a
   <code class="literal">
    [NOT] IN
   </code>
   or
   <code class="literal">
    [NOT] EXISTS
   </code>
   subquery predicate, provided that the statement does not use
   <code class="literal">
    ORDER BY
   </code>
   or
   <code class="literal">
    LIMIT
   </code>
   , and
          that subquery materialization is allowed by an optimizer hint
          or by the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   setting.
  </p>
 </div>
 <br/>
</div>
