Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions Note Prior to MySQL 8.0.22, if a derived table was materialized but not merged, MySQL materialized the entire table, then qualified all of the resulting rows with the  WHERE condition. This is still the case if derived condition pushdown is not enabled, or cannot be employed for some other reason. Outer  WHERE  conditions can be pushed down to derived materialized tables under the following circumstances: For example, the query  SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11  is rewritten as  SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt . For example,  SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100  is rewritten following derived condition pushdown as  SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt . For example, the query  SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10  is rewritten as  SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt . In the event that the outer  WHERE  condition has predicates referencing columns which are part of the GROUP BY  as well as predicates referencing columns which are not, predicates of the former sort are pushed down as  WHERE  conditions, while those of the latter type are pushed down as  HAVING conditions. For example, in the query  SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 AND sum > 100 , the predicate  i > 10  in the outer WHERE  clause references a  GROUP BY  column, whereas the predicate  sum > 100  does not reference any  GROUP BY  column. Thus the derived table pushdown optimization causes the query to be rewritten in a manner similar to what is shown here: •When the derived table uses a  GROUP BY  and the columns in the outer  WHERE  condition are  GROUP BY columns, the  WHERE  conditions referencing those columns can be pushed down directly to the derived table. SELECT * FROM ( SELECT i, j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i, j HAVING sum > 100 ) AS dt; To enable derived condition pushdown, the  optimizer_switch  system variable’s derived_condition_pushdown  flag (added in this release) must be set to  on , which is the default setting. If this optimization is disabled by  optimizer_switch , you can enable it for a specific query using the  DERIVED_CONDITION_PUSHDOWN  optimizer hint. To disable the optimization for a given query, use the  NO_DERIVED_CONDITION_PUSHDOWN  optimizer hint. The following restrictions and limitations apply to the derived table condition pushdown optimization: 1707 •When the derived table uses no aggregate or window functions, the outer  WHERE  condition can be pushed down to it directly. This includes  WHERE  conditions having multiple predicates joined with  AND , OR , or both. •When the derived table has a  GROUP BY  and uses no window functions, an outer  WHERE  condition referencing one or more columns which are not part of the  GROUP BY  can be pushed down to the derived table as a  HAVING  condition.