PolarDB for MySQL supports the WHERE to derived tables feature. You can use the feature to transform complex SQL queries that meet specific conditions. If a derived table cannot be merged into an outer query, the outer WHERE condition is pushed down to the derived table to reduce the number of rows that need to be processed by an SQL statement. This improves the query performance of the SQL statement.
Prerequisites
Your cluster is a PolarDB for MySQL 8.0 cluster whose revision version is 8.0.2.2.13 or later. For information about how to view the cluster version, see the Query the engine version.
Limits
If all columns or equivalent columns in a conditional expression come from a materialized derived table, the condition can be pushed down to the materialized table.
If limits are imposed on a materialized derived table, conditions cannot be pushed down.
If the following situations exist in a column of an outer WHERE conditional expression or a column mapped to a materialized table, the condition cannot be pushed down:
The value in the column is generated by a subquery or is non-deterministic. This means that different results may be generated under the same input conditions.
The column is a stored procedure or storage function.
Background information
In scenarios where complex queries are involved, databases need to provide powerful pushdown capabilities to accelerate the queries. PolarDB for MySQL enhances the WHERE to derived tables feature based on the condition pushdown feature of MySQL Community Edition to provide better pushdown capabilities. The enhancement involves the following aspects:
The equivalent conditions can be transferred.
If a derived table is a union, conditions that meet the specified requirements can be pushed down to the corresponding part of the union.
The WHERE to Derived Tables feature can be used together with the HAVING to WHERE feature to further cascade down conditions that are pushed down based on equivalence relation.
Usage
You can configure the loose_derived_cond_pushdown_mode parameter to push down WHERE conditions to derived tables. The following table describes this parameter. For more information, see Specify cluster and node parameters.
You can also run the following commands to push down WHERE conditions to derived tables in a session-level database connection:
SET optimizer_switch="derived_condition_pushdown=on";
SET derived_cond_pushdown_mode=on;Parameters
Parameter | Level | Description |
loose_derived_cond_pushdown_mode | Global | Specifies whether to push down WHERE conditions to derived tables. Valid values:
|
Examples
Push down conditions from WHERE clauses to derived tables.
Example 1:
SELECT *
FROM t1, (
SELECT x
FROM t2
GROUP BY x
) d_tab, t2
WHERE t1.a = d_tab.x
AND t1.a > 6;Sample result:
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:
SELECT f1
FROM (
SELECT (
SELECT f1
FROM t1
LIMIT 1
) AS f1
FROM t1
UNION
SELECT f2
FROM t2
) dt
WHERE f1 = 1;Sample result:
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:
SELECT *
FROM (
SELECT f1, f2
FROM t1
) dt
GROUP BY f1
HAVING f1 < 3
AND f2 > 11
AND MAX(f3) > 12;Sample result:
SELECT *
FROM (
SELECT f1, f2
FROM t1
WHERE f1 < 3
) dt
WHERE f1 < 3
GROUP BY f1
HAVING f2 > 11
AND MAX(f3) > 12;