MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

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

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.