Optimizing SELECT Statements As mentioned previously, a nondeterministic expression in the  WHERE  clause might prevent optimizations and result in a table scan. However, it may be possible to partially optimize the  WHERE  clause if other expressions are deterministic. For example: If the optimizer can use  partial_key  to reduce the set of rows selected,  RAND()  is executed fewer times, which diminishes the effect of nondeterminism on optimization. Window functions affect the strategies the optimizer considers: SELECT * FROM t WHERE partial_key=5 AND some_column=RAND(); 8.2.1.21 Window Function Optimization •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: Consequently, MySQL aggregates inside the subquery, treating  t1.b  as a constant and returning the count of rows of  t2 . SELECT * FROM t1 WHERE t1.a = (SELECT COUNT(t1.b) FROM t2); 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 Replacing  WHERE  with  HAVING  results in an error: 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. 1691