Movatterモバイル変換

MySQL 9.4 Reference Manual

10.2.2.5 Derived Condition Pushdown Optimization

MySQL supports derived condition pushdown for eligible subqueries. For a query such asSELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i >constant, it is possible in many cases to push the outerWHERE condition down to the derived table, in this case resulting inSELECT * 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 outerWHERE 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.

OuterWHERE conditions can be pushed down to derived materialized tables under the following circumstances:

When the derived table uses no aggregate or window functions, the outerWHERE condition can be pushed down to it directly. This includesWHERE conditions having multiple predicates joined withAND,OR, or both.
For example, the querySELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11 is rewritten asSELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt.
When the derived table has aGROUP BY and uses no window functions, an outerWHERE condition referencing one or more columns which are not part of theGROUP BY can be pushed down to the derived table as aHAVING 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 asSELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt.
When the derived table uses aGROUP BY and the columns in the outerWHERE condition areGROUP BY columns, theWHERE conditions referencing those columns can be pushed down directly to the derived table.
For example, the querySELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 is rewritten asSELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt.
In the event that the outerWHERE condition has predicates referencing columns which are part of theGROUP BY as well as predicates referencing columns which are not, predicates of the former sort are pushed down asWHERE conditions, while those of the latter type are pushed down asHAVING conditions. For example, in the querySELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 AND sum > 100, the predicatei > 10 in the outerWHERE clause references aGROUP BY column, whereas the predicatesum > 100 does not reference anyGROUP 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, theoptimizer_switch system variable'sderived_condition_pushdown flag (added in this release) must be set toon, which is the default setting. If this optimization is disabled byoptimizer_switch, you can enable it for a specific query using theDERIVED_CONDITION_PUSHDOWN optimizer hint. To disable the optimization for a given query, use theNO_DERIVED_CONDITION_PUSHDOWN optimizer hint.

The following restrictions and limitations apply to the derived table condition pushdown optimization:

The derived table condition pushdown optimization can be employed withUNION queries, with the following exceptions:
- Condition pushdown cannot be used with aUNION query if any materialized derived table that is part of theUNION is a recursive common table expression (seeRecursive Common Table Expressions).
- Conditions containing nondeterministic expressions cannot be pushed down to a derived table.
The derived table cannot use aLIMIT 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 formderived_column > ?. If a derived column in an outerWHERE condition is an expression having a? in the underlying derived table, this condition cannot be pushed down.
For a query in which the condition is on the tables of a view created usingALGORITHM=TEMPTABLE instead of on the view itself, the multiple equality is not recognized at resolution, and thus the condition cannot be not pushed down. This because, when optimizing a query, condition pushdown takes place during resolution phase while multiple equality propagation occurs during optimization.
This is not an issue in such cases for a view usingALGORITHM=MERGE, where the equality can be propagated and the condition pushed down.
A condition cannot be pushed down if the derived table'sSELECT list contain any assignments to user variables.

PREV HOME UP NEXT