<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="function-optimization">
      </a>
      10.2.1.20 Function Call Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228576160">
  </a>
  <a class="indexterm" name="idm46045228574688">
  </a>
  <a class="indexterm" name="idm46045228573184">
  </a>
  <a class="indexterm" name="idm46045228571680">
  </a>
  <p>
   MySQL functions are tagged internally as deterministic or
          nondeterministic. A function is nondeterministic if, given
          fixed values for its arguments, it can return different
          results for different invocations. Examples of
          nondeterministic functions:
   <a class="link" href="mathematical-functions.html#function_rand">
    <code class="literal">
     RAND()
    </code>
   </a>
   ,
   <a class="link" href="miscellaneous-functions.html#function_uuid">
    <code class="literal">
     UUID()
    </code>
   </a>
   .
  </p>
  <p>
   If a function is tagged nondeterministic, a reference to it in
          a
   <code class="literal">
    WHERE
   </code>
   clause is evaluated for every row
          (when selecting from one table) or combination of rows (when
          selecting from a multiple-table join).
  </p>
  <p>
   MySQL also determines when to evaluate functions based on
          types of arguments, whether the arguments are table columns or
          constant values. A deterministic function that takes a table
          column as argument must be evaluated whenever that column
          changes value.
  </p>
  <p>
   Nondeterministic functions may affect query performance. For
          example, some optimizations may not be available, or more
          locking might be required. The following discussion uses
   <a class="link" href="mathematical-functions.html#function_rand">
    <code class="literal">
     RAND()
    </code>
   </a>
   but applies to other
          nondeterministic functions as well.
  </p>
  <p>
   Suppose that a table
   <code class="literal">
    t
   </code>
   has this definition:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa49956727"><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">TABLE</span> t <span class="token punctuation">(</span>id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> col_a <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Consider these two queries:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68297795"><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> t <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token function">POW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">+</span> <span class="token function">RAND</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">49</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Both queries appear to use a primary key lookup because of the
          equality comparison against the primary key, but that is true
          only for the first of them:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The first query always produces a maximum of one row
              because
      <a class="link" href="mathematical-functions.html#function_pow">
       <code class="literal">
        POW()
       </code>
      </a>
      with constant
              arguments is a constant value and is used for index
              lookup.
     </p>
    </li>
    <li class="listitem">
     <p>
      The second query contains an expression that uses the
              nondeterministic function
      <a class="link" href="mathematical-functions.html#function_rand">
       <code class="literal">
        RAND()
       </code>
      </a>
      , which is not
              constant in the query but in fact has a new value for
              every row of table
      <code class="literal">
       t
      </code>
      . Consequently, the
              query reads every row of the table, evaluates the
              predicate for each row, and outputs all rows for which the
              primary key matches the random value. This might be zero,
              one, or multiple rows, depending on the
      <code class="literal">
       id
      </code>
      column values and the values in the
      <a class="link" href="mathematical-functions.html#function_rand">
       <code class="literal">
        RAND()
       </code>
      </a>
      sequence.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The effects of nondeterminism are not limited to
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements. This
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statement uses a
          nondeterministic function to select rows to be modified:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa58408610"><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">UPDATE</span> t <span class="token keyword">SET</span> col_a <span class="token operator">=</span> <em class="replaceable">some_expr</em> <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">+</span> <span class="token function">RAND</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">49</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Presumably the intent is to update at most a single row for
          which the primary key matches the expression. However, it
          might update zero, one, or multiple rows, depending on the
   <code class="literal">
    id
   </code>
   column values and the values in the
   <a class="link" href="mathematical-functions.html#function_rand">
    <code class="literal">
     RAND()
    </code>
   </a>
   sequence.
  </p>
  <p>
   The behavior just described has implications for performance
          and replication:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Because a nondeterministic function does not produce a
              constant value, the optimizer cannot use strategies that
              might otherwise be applicable, such as index lookups. The
              result may be a table scan.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       InnoDB
      </code>
      might escalate to a range-key
              lock rather than taking a single row lock for one matching
              row.
     </p>
    </li>
    <li class="listitem">
     <p>
      Updates that do not execute deterministically are unsafe
              for replication.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The difficulties stem from the fact that the
   <a class="link" href="mathematical-functions.html#function_rand">
    <code class="literal">
     RAND()
    </code>
   </a>
   function is evaluated
          once for every row of the table. To avoid multiple function
          evaluations, use one of these techniques:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Move the expression containing the nondeterministic
              function to a separate statement, saving the value in a
              variable. In the original statement, replace the
              expression with a reference to the variable, which the
              optimizer can treat as a constant value:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28816932"><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">SET</span> <span class="token variable">@keyval</span> <span class="token operator">=</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">+</span> <span class="token function">RAND</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">49</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> t <span class="token keyword">SET</span> col_a <span class="token operator">=</span> <em class="replaceable">some_expr</em> <span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token variable">@keyval</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Assign the random value to a variable in a derived table.
              This technique causes the variable to be assigned a value,
              once, prior to its use in the comparison in the
      <code class="literal">
       WHERE
      </code>
      clause:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46802398"><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">UPDATE</span> <span class="token comment" spellcheck="true">/*+ NO_MERGE(dt) */</span> t<span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">+</span> <span class="token function">RAND</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">49</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> r<span class="token punctuation">)</span> <span class="token keyword">AS</span> dt
<span class="token keyword">SET</span> col_a <span class="token operator">=</span> <em class="replaceable">some_expr</em> <span class="token keyword">WHERE</span> id <span class="token operator">=</span> dt<span class="token punctuation">.</span>r<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   As mentioned previously, a nondeterministic expression in the
   <code class="literal">
    WHERE
   </code>
   clause might prevent optimizations
          and result in a table scan. However, it may be possible to
          partially optimize the
   <code class="literal">
    WHERE
   </code>
   clause if
          other expressions are deterministic. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa29088753"><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> t <span class="token keyword">WHERE</span> partial_key<span class="token operator">=</span><span class="token number">5</span> <span class="token operator">AND</span> some_column<span class="token operator">=</span><span class="token function">RAND</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If the optimizer can use
   <code class="literal">
    partial_key
   </code>
   to
          reduce the set of rows selected,
   <a class="link" href="mathematical-functions.html#function_rand">
    <code class="literal">
     RAND()
    </code>
   </a>
   is executed fewer times,
          which diminishes the effect of nondeterminism on optimization.
  </p>
 </div>
 <br/>
</div>
