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
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
OVERclauses contain identical window definitions). The workaround is to define the window in a
WINDOWclause and refer to the window name in the
An aggregate function not used as a window function is
aggregated in the outermost possible query. For example, in
this query, MySQL sees that
something that cannot exist in the outer query because of its
placement in the
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
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
exist in the
HAVING, and so makes the outer
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
system variable, which determines whether to compute window
operations without loss of precision. By default,
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
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_precisioncan be disabled to permit inverse aggregation.
For nonfloating-point data types, inverse aggregation is always safe and is used regardless of the
For evaluation of the variance functions
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,
can be disabled to permit optimized mode.