MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0 Derived Condition Pushdown Optimization

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 > constant, it is possible in many cases to push the the outer WHERE condition down to the derived table, in this case resulting in SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt. When a derived table cannot be merged into the outer query (for example, if the derived table uses aggregation), pushing the outer 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:

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: