Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.3Mb
PDF (A4) - 40.4Mb
PDF (RPM) - 39.7Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 244.1Kb
Man Pages (Zip) - 349.3Kb
Info (Gzip) - 3.8Mb
Info (Zip) - 3.8Mb
Excerpts from this Manual

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

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;

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 derived_column > ?. If a derived column in an outer WHERE condition is an expression having a ? in the underlying derived table, this condition cannot be pushed down.