MySQL 8.0.22 and later supports derived condition pushdown for
eligible subqueries. For a query such as ```
SELECT *
FROM (SELECT i, j FROM t1) AS dt WHERE i >
```

, it is possible
in many cases to push the the outer `constant`

`WHERE`

condition down to the derived table, in this case resulting in
```
SELECT * FROM (SELECT i, j FROM t1 WHERE i >
```

. When a
derived table cannot be merged into the outer query (for
example, if the derived table uses aggregation), pushing the
outer * constant*) AS dt

`WHERE`

condition down to the derived
table should decrease the number of rows that need to be
processed and thus speed up execution of the query.
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:

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.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`

.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.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`

.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.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:`SELECT * FROM ( SELECT i, j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i, j HAVING sum > 100 ) AS dt;`

If the derived table uses a window function and the outer

`WHERE`

references columns used in the window function's`PARTITION`

clause, the WHERE condition can be pushed down to a`HAVING`

condition if there is a`GROUP BY`

; otherwise, it can be pushed to the`WHERE`

condition in the derived table. For example, the query`SELECT * FROM (SELECT i, j, SUM(k) OVER (PARTITION BY j) AS sum FROM t1) AS dt WHERE j > 10`

can be rewritten as`SELECT * FROM (SELECT i, j, SUM(k) OVER (PARTITION BY j) AS sum FROM t1 WHERE j > 10) AS dt`

.In cases in which the derived table uses a window function, predicates in the outer

`WHERE`

clause can sometimes be pushed down separately according to the rules already given. In the query`SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum FROM t1 GROUP BY i, j) AS dt WHERE i > 10 AND min < 3`

, the predicate`i > 10`

references the column used in`PARTITION BY`

, and so can be pushed down directly;`min < 3`

does not reference any columns in either of the`PARTITION BY`

or`GROUP BY`

clauses but can be pushed down as a`HAVING`

condition. This means that the query can be rewritten like this:`SELECT * FROM ( SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum FROM t1 WHERE i > 10 GROUP BY i, j HAVING MIN < 3 ) 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:

The optimization cannot be used if the derived table contains

`UNION`

.The derived table cannot use a

`LIMIT`

clause.Conditions containing subqueries cannot be pushed down.

The optimization cannot be used if the derived table is an inner table of an outer join.

If a materialized derived table is a common table expression, conditions are not pushed down to it if it is referenced multiple times.

Conditions using parameters can be pushed down if the condition is of the form

. If a derived column in an outer> ?`derived_column`

`WHERE`

condition is an expression having a`?`

in the underlying derived table, this condition cannot be pushed down.