<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="window-function-optimization">
      </a>
      10.2.1.21 Window Function Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228524720">
  </a>
  <a class="indexterm" name="idm46045228523232">
  </a>
  <p>
   Window functions affect the strategies the optimizer
          considers:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Derived table merging for a subquery is disabled if the
              subquery has window functions. The subquery is always
              materialized.
     </p>
    </li>
    <li class="listitem">
     <p>
      Semijoins are not applicable to window function
              optimization because semijoins apply to subqueries in
      <code class="literal">
       WHERE
      </code>
      and
      <code class="literal">
       JOIN ...
              ON
      </code>
      , which cannot contain window functions.
     </p>
    </li>
    <li class="listitem">
     <p>
      The optimizer processes multiple windows that have the
              same ordering requirements in sequence, so sorting can be
              skipped for windows following the first one.
     </p>
    </li>
    <li class="listitem">
     <p>
      The optimizer makes no attempt to merge windows that could
              be evaluated in a single step (for example, when multiple
      <code class="literal">
       OVER
      </code>
      clauses contain identical window
              definitions). The workaround is to define the window in a
      <code class="literal">
       WINDOW
      </code>
      clause and refer to the window
              name in the
      <code class="literal">
       OVER
      </code>
      clauses.
     </p>
    </li>
   </ul>
  </div>
  <p>
   An aggregate function not used as a window function is
          aggregated in the outermost possible query. For example, in
          this query, MySQL sees that
   <code class="literal">
    COUNT(t1.b)
   </code>
   is
          something that cannot exist in the outer query because of its
          placement in the
   <code class="literal">
    WHERE
   </code>
   clause:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa81995684"><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 operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>t1<span class="token punctuation">.</span>b<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Consequently, MySQL aggregates inside the subquery, treating
   <code class="literal">
    t1.b
   </code>
   as a constant and returning the count
          of rows of
   <code class="literal">
    t2
   </code>
   .
  </p>
  <p>
   Replacing
   <code class="literal">
    WHERE
   </code>
   with
   <code class="literal">
    HAVING
   </code>
   results in an error:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88770672"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">HAVING</span> t1<span class="token punctuation">.</span>a <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>t1<span class="token punctuation">.</span>b<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1140 (42000)<span class="token punctuation">:</span> In aggregated query without GROUP BY, expression #1
of SELECT list contains nonaggregated column 'test.t1.a'; this is
incompatible with sql_mode=only_full_group_by</span></code></pre>
  </div>
  <p>
   The error occurs because
   <code class="literal">
    COUNT(t1.b)
   </code>
   can
          exist in the
   <code class="literal">
    HAVING
   </code>
   , and so makes the outer
          query aggregated.
  </p>
  <p>
   Window functions (including aggregate functions used as window
          functions) do not have the preceding complexity. They always
          aggregate in the subquery where they are written, never in the
          outer query.
  </p>
  <p>
   Window function evaluation may be affected by the value of the
   <a class="link" href="server-system-variables.html#sysvar_windowing_use_high_precision">
    <code class="literal">
     windowing_use_high_precision
    </code>
   </a>
   system variable, which determines whether to compute window
          operations without loss of precision. By default,
   <a class="link" href="server-system-variables.html#sysvar_windowing_use_high_precision">
    <code class="literal">
     windowing_use_high_precision
    </code>
   </a>
   is enabled.
  </p>
  <p>
   For some moving frame aggregates, the inverse aggregate
          function can be applied to remove values from the aggregate.
          This can improve performance but possibly with a loss of
          precision. For example, adding a very small floating-point
          value to a very large value causes the very small value to be
   <span class="quote">
    “
    <span class="quote">
     hidden
    </span>
    ”
   </span>
   by the large value. When inverting the
          large value later, the effect of the small value is lost.
  </p>
  <p>
   Loss of precision due to inverse aggregation is a factor only
          for operations on floating-point (approximate-value) data
          types. For other types, inverse aggregation is safe; this
          includes
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   , which permits
          a fractional part but is an exact-value type.
  </p>
  <p>
   For faster execution, MySQL always uses inverse aggregation
          when it is safe:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For floating-point values, inverse aggregation is not
              always safe and might result in loss of precision. The
              default is to avoid inverse aggregation, which is slower
              but preserves precision. If it is permissible to sacrifice
              safety for speed,
      <a class="link" href="server-system-variables.html#sysvar_windowing_use_high_precision">
       <code class="literal">
        windowing_use_high_precision
       </code>
      </a>
      can be disabled to permit inverse aggregation.
     </p>
    </li>
    <li class="listitem">
     <p>
      For nonfloating-point data types, inverse aggregation is
              always safe and is used regardless of the
      <a class="link" href="server-system-variables.html#sysvar_windowing_use_high_precision">
       <code class="literal">
        windowing_use_high_precision
       </code>
      </a>
      value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_windowing_use_high_precision">
       <code class="literal">
        windowing_use_high_precision
       </code>
      </a>
      has no effect on
      <a class="link" href="aggregate-functions.html#function_min">
       <code class="literal">
        MIN()
       </code>
      </a>
      and
      <a class="link" href="aggregate-functions.html#function_max">
       <code class="literal">
        MAX()
       </code>
      </a>
      , which do not use
              inverse aggregation in any case.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For evaluation of the variance functions
   <a class="link" href="aggregate-functions.html#function_stddev-pop">
    <code class="literal">
     STDDEV_POP()
    </code>
   </a>
   ,
   <a class="link" href="aggregate-functions.html#function_stddev-samp">
    <code class="literal">
     STDDEV_SAMP()
    </code>
   </a>
   ,
   <a class="link" href="aggregate-functions.html#function_var-pop">
    <code class="literal">
     VAR_POP()
    </code>
   </a>
   ,
   <a class="link" href="aggregate-functions.html#function_var-samp">
    <code class="literal">
     VAR_SAMP()
    </code>
   </a>
   , and their synonyms,
          evaluation can occur in optimized mode or default mode.
          Optimized mode may produce slightly different results in the
          last significant digits. If such differences are permissible,
   <a class="link" href="server-system-variables.html#sysvar_windowing_use_high_precision">
    <code class="literal">
     windowing_use_high_precision
    </code>
   </a>
   can be disabled to permit optimized mode.
  </p>
  <a class="indexterm" name="idm46045228477664">
  </a>
  <a class="indexterm" name="idm46045228476176">
  </a>
  <p>
   For
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   , windowing
          execution plan information is too extensive to display in
          traditional output format. To see windowing information, use
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
          FORMAT=JSON
    </code>
   </a>
   and look for the
   <code class="literal">
    windowing
   </code>
   element.
  </p>
 </div>
 <br/>
</div>
