10.2.2.5 Derived Condition Pushdown Optimization (original) (raw)

10.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 outer WHERE 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 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 theWHERE 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:

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:

CREATE TABLE t1 (  
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  
  c1 INT,  
  KEY i1 (c1)  
);  
CREATE TABLE t2 (  
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  
  c1 INT,  
  KEY i1 (c1)  
);  
CREATE OR REPLACE VIEW v AS  
     SELECT id, c1 FROM t1  
     UNION ALL  
     SELECT id, c1 FROM t2;

As be seen in the output ofEXPLAIN, a condition present in the top level of a query such asSELECT * FROM v WHERE c1 = 12 can now be pushed down to both query blocks in the derived table: 

mysql> EXPLAIN FORMAT=TREE SELECT * FROM v WHERE c1 = 12\G  
*************************** 1. row ***************************  
EXPLAIN: -> Table scan on v  (cost=1.26..2.52 rows=2)  
    -> Union materialize  (cost=2.16..3.42 rows=2)  
        -> Covering index lookup on t1 using i1 (c1=12)  (cost=0.35 rows=1)  
        -> Covering index lookup on t2 using i1 (c1=12)  (cost=0.35 rows=1)  
1 row in set (0.00 sec)

In MySQL 8.0.29 and later, the derived table condition pushdown optimization can be employed withUNION queries, with the following exceptions: