<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="view-algorithms">
      </a>
      27.5.2 View Processing Algorithms
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045081886144">
  </a>
  <p>
   The optional
   <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>
   or
   <a class="link" href="alter-view.html" title="15.1.11 ALTER VIEW Statement">
    <code class="literal">
     ALTER VIEW
    </code>
   </a>
   is a MySQL extension to
      standard SQL. It affects how MySQL processes the view.
   <code class="literal">
    ALGORITHM
   </code>
   takes three values:
   <code class="literal">
    MERGE
   </code>
   ,
   <code class="literal">
    TEMPTABLE
   </code>
   , or
   <code class="literal">
    UNDEFINED
   </code>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For
      <code class="literal">
       MERGE
      </code>
      , the text of a statement that
          refers to the view and the view definition are merged such
          that parts of the view definition replace corresponding parts
          of the statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <code class="literal">
       TEMPTABLE
      </code>
      , the results from the view
          are retrieved into a temporary table, which then is used to
          execute the statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <code class="literal">
       UNDEFINED
      </code>
      , MySQL chooses which
          algorithm to use. It prefers
      <code class="literal">
       MERGE
      </code>
      over
      <code class="literal">
       TEMPTABLE
      </code>
      if possible, because
      <code class="literal">
       MERGE
      </code>
      is usually more efficient and because
          a view cannot be updatable if a temporary table is used.
     </p>
    </li>
    <li class="listitem">
     <p>
      If no
      <code class="literal">
       ALGORITHM
      </code>
      clause is present, the
          default algorithm is determined by the value of 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. For additional discussion, see
      <a class="xref" href="derived-table-optimization.html" title="10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization">
       Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions
          with Merging or Materialization”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   A reason to specify
   <code class="literal">
    TEMPTABLE
   </code>
   explicitly is
      that locks can be released on underlying tables after the
      temporary table has been created and before it is used to finish
      processing the statement. This might result in quicker lock
      release than the
   <code class="literal">
    MERGE
   </code>
   algorithm so that other
      clients that use the view are not blocked as long.
  </p>
  <p>
   A view algorithm can be
   <code class="literal">
    UNDEFINED
   </code>
   for three
      reasons:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      No
      <code class="literal">
       ALGORITHM
      </code>
      clause is present in the
      <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
       <code class="literal">
        CREATE VIEW
       </code>
      </a>
      statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
       <code class="literal">
        CREATE VIEW
       </code>
      </a>
      statement has
          an explicit
      <code class="literal">
       ALGORITHM = UNDEFINED
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ALGORITHM = MERGE
      </code>
      is specified for a view
          that can be processed only with a temporary table. In this
          case, MySQL generates a warning and sets the algorithm to
      <code class="literal">
       UNDEFINED
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   As mentioned earlier,
   <code class="literal">
    MERGE
   </code>
   is handled by
      merging corresponding parts of a view definition into the
      statement that refers to the view. The following examples briefly
      illustrate how the
   <code class="literal">
    MERGE
   </code>
   algorithm works. The
      examples assume that there is a view
   <code class="literal">
    v_merge
   </code>
   that has this definition:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20922127"><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">MERGE</span> <span class="token keyword">VIEW</span> v_merge <span class="token punctuation">(</span>vc1<span class="token punctuation">,</span> vc2<span class="token punctuation">)</span> <span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> c3 <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Example 1: Suppose that we issue this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa504665"><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> v_merge<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL handles the statement as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       v_merge
      </code>
      becomes
      <code class="literal">
       t
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       *
      </code>
      becomes
      <code class="literal">
       vc1, vc2
      </code>
      ,
          which corresponds to
      <code class="literal">
       c1, c2
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      The view
      <code class="literal">
       WHERE
      </code>
      clause is added
     </p>
    </li>
   </ul>
  </div>
  <p>
   The resulting statement to be executed becomes:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa48748879"><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> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> c3 <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Example 2: Suppose that we issue this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa80698225"><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> v_merge <span class="token keyword">WHERE</span> vc1 <span class="token operator">&lt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This statement is handled similarly to the previous one, except
      that
   <code class="literal">
    vc1 &lt; 100
   </code>
   becomes
   <code class="literal">
    c1 &lt;
      100
   </code>
   and the view
   <code class="literal">
    WHERE
   </code>
   clause is
      added to the statement
   <code class="literal">
    WHERE
   </code>
   clause using an
   <a class="link" href="logical-operators.html#operator_and">
    <code class="literal">
     AND
    </code>
   </a>
   connective (and parentheses are
      added to make sure the parts of the clause are executed with
      correct precedence). The resulting statement to be executed
      becomes:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa18534644"><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> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> <span class="token punctuation">(</span>c3 <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">AND</span> <span class="token punctuation">(</span>c1 <span class="token operator">&lt;</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Effectively, the statement to be executed has a
   <code class="literal">
    WHERE
   </code>
   clause of this form:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa95337261"><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">WHERE</span> <span class="token punctuation">(</span><span class="token keyword">select</span> <span class="token keyword">WHERE</span><span class="token punctuation">)</span> <span class="token operator">AND</span> <span class="token punctuation">(</span><span class="token keyword">view</span> <span class="token keyword">WHERE</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   If the
   <code class="literal">
    MERGE
   </code>
   algorithm cannot be used, a
      temporary table must be used instead. Constructs that prevent
      merging are the same as those that prevent merging in derived
      tables and common table expressions. Examples are
   <code class="literal">
    SELECT
      DISTINCT
   </code>
   or
   <code class="literal">
    LIMIT
   </code>
   in the subquery.
      For details, see
   <a class="xref" href="derived-table-optimization.html" title="10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization">
    Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions
          with Merging or Materialization”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
