Push down WHERE conditions to derived tables to improve query performance - PolarDB

When a derived table cannot be merged into the outer query, the database materializes the entire derived table first, then filters rows using the WHERE condition. For complex queries with large derived tables, this approach processes far more data than necessary. PolarDB for MySQL pushes WHERE conditions directly into derived tables during materialization, reducing the number of rows processed and improving query performance.

Supported versions

To use this feature, your cluster must run one of the following versions. To check your version, see Query the engine version.

MySQL 8.0.1, revision version 8.0.1.1.42 or later
MySQL 8.0.2, revision version 8.0.2.2.10 or later

How it works

PolarDB for MySQL extends the condition pushdown capability of MySQL Community Edition with the following enhancements:

Equivalence transfer: Conditions are transferred through equivalent column relationships. If t1.a = d_tab.x and t1.a > 6, the condition x > 6 is derived and pushed into the derived table.
UNION support: Conditions are pushed into the matching branch of a UNION derived table.
Cascade with HAVING to WHERE: Works together with the HAVING to WHERE feature to further push down conditions through equivalence chains.

Limitations

A WHERE condition can be pushed down to a derived table only when all of the following are true:

All columns (or their equivalents) in the condition come from a materialized derived table.
The derived table has no LIMIT clause.
No column in the outer WHERE condition or in the mapped materialized table column:
- Is generated by a subquery or has a non-deterministic value (same input may return different results).
- Is a stored procedure or stored function.

Configure WHERE to derived tables

Set the loose_derived_cond_pushdown_mode parameter to control this feature globally. For instructions on setting parameters, see Configure cluster and node parameters.

Parameter	Level	Description
`loose_derived_cond_pushdown_mode`	Global	Controls whether WHERE conditions are pushed down to derived tables. Valid values: `OFF` (disabled), `ON` (enabled on all nodes), `REPLICA_ON` (enabled on read-only nodes only, default).

To enable the feature for the current session only, run:

SET optimizer_switch="derived_condition_pushdown=on";
SET derived_cond_pushdown_mode=on;

Examples

The following examples show how PolarDB for MySQL transforms queries when condition pushdown is active. Each example shows the original query and its optimized equivalent.

Example 1: Pushdown through equivalence transfer

When the outer WHERE clause contains an equivalence condition (t1.a = d_tab.x), PolarDB derives and pushes the implied condition (x > 6) into the derived table. This eliminates rows from the derived table before the join, rather than filtering after full materialization.

Original query:

SELECT *
FROM t1, (
           SELECT x
           FROM t2
           GROUP BY x
         ) d_tab, t2
WHERE t1.a = d_tab.x
        AND t1.a > 6;

Optimized query (the condition x > 6 is pushed into the derived table):

SELECT *
FROM t1, (
           SELECT x
           FROM t2
           WHERE x > 6
           GROUP BY x
         ) d_tab
WHERE t1.a = d_tab.x
        AND t1.a > 6;

Example 2: Pushdown into a UNION derived table

When the derived table is a UNION, PolarDB pushes the condition into eligible branches only. In this example, the condition f2 = 1 is pushed into the second branch of the UNION.

Original query:

SELECT f1
FROM (
    SELECT (
            SELECT f1
            FROM t1
            LIMIT 1
           ) AS f1
    FROM t1
    UNION
    SELECT f2
    FROM t2
) dt
WHERE f1 = 1;

Optimized query (the condition f2 = 1 is pushed into the eligible UNION branch):

SELECT f1
FROM (
    SELECT (
            SELECT f1
            FROM t1
            LIMIT 1
           ) AS f1
    FROM t1
    UNION
    SELECT f2
    FROM t2
    WHERE f2 = 1
) dt
WHERE f1 = 1;

Example 3: Cascade with HAVING to WHERE

When used together with the HAVING to WHERE feature, conditions pushed down from HAVING can be further cascaded into the derived table. In this example, f1 < 3 moves from HAVING to WHERE, then into the derived table.

Original query:

SELECT *
FROM (
    SELECT f1, f2
    FROM t1
) dt
GROUP BY f1
HAVING f1 < 3
AND f2 > 11
AND MAX(f3) > 12;

Optimized query (f1 < 3 is pushed into the derived table; f2 > 11 and MAX(f3) > 12 remain in HAVING):

SELECT *
FROM (
    SELECT f1, f2
    FROM t1
    WHERE f1 < 3
) dt
WHERE f1 < 3
GROUP BY f1
HAVING f2 > 11
AND MAX(f3) > 12;

What's next

Condition pushdown (HAVING to WHERE) — use together with WHERE to derived tables for deeper condition cascading.
Configure cluster and node parameters — set loose_derived_cond_pushdown_mode globally.
Query the engine version — confirm your cluster version before enabling the feature.