10.2.1.21 Window Function Optimization

Window functions affect the strategies the optimizer considers:

• Derived table merging for a subquery is disabled if the subquery has window functions. The subquery is always materialized.

• Semijoins are not applicable to window function optimization because semijoins apply to subqueries in WHERE and JOIN ... ON, which cannot contain window functions.

• The optimizer processes multiple windows that have the same ordering requirements in sequence, so sorting can be skipped for windows following the first one.

• The optimizer makes no attempt to merge windows that could be evaluated in a single step (for example, when multiple OVER clauses contain identical window definitions). The workaround is to define the window in a WINDOW clause and refer to the window name in the OVER clauses.

An aggregate function not used as a window function is aggregated in the outermost possible query. For example, in this query, MySQL sees that COUNT(t1.b) is something that cannot exist in the outer query because of its placement in the WHERE clause:

SELECT * FROM t1 WHERE t1.a = (SELECT COUNT(t1.b) FROM t2);

Consequently, MySQL aggregates inside the subquery, treating t1.b as a constant and returning the count of rows of t2.

Replacing WHERE with HAVING results in an error:

mysql> SELECT * FROM t1 HAVING t1.a = (SELECT COUNT(t1.b) FROM t2);
ERROR 1140 (42000): 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

The error occurs because COUNT(t1.b) can exist in the HAVING, and so makes the outer query aggregated.

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.

Window function evaluation may be affected by the value of the windowing_use_high_precision system variable, which determines whether to compute window operations without loss of precision. By default, windowing_use_high_precision is enabled.

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 “hidden” by the large value. When inverting the large value later, the effect of the small value is lost.

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 DECIMAL, which permits a fractional part but is an exact-value type.

For faster execution, MySQL always uses inverse aggregation when it is safe:

• 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, windowing_use_high_precision can be disabled to permit inverse aggregation.

• For nonfloating-point data types, inverse aggregation is always safe and is used regardless of the windowing_use_high_precision value.

• windowing_use_high_precision has no effect on MIN() and MAX(), which do not use inverse aggregation in any case.

For evaluation of the variance functions STDDEV_POP(), STDDEV_SAMP(), VAR_POP(), VAR_SAMP(), 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, windowing_use_high_precision can be disabled to permit optimized mode.

For EXPLAIN, windowing execution plan information is too extensive to display in traditional output format. To see windowing information, use EXPLAIN FORMAT=JSON and look for the windowing element.