PolarDB:Query rewrites

The query rewrite component converts SQL statements into more efficient forms based on relational model equivalence. This shortens the time that the optimizer spends finding the optimal execution plan and greatly improves query execution efficiency. Some of the most common query transformations, such as subquery optimization and condition pushdown, occur at this stage.

PolarDB for MySQL provides a wide range of cost-based query rewrite features that cover many scenarios. This cost-based approach allows PolarDB to determine the optimal query rewrite method.

In addition to the query rewrite features available in community MySQL, the following topics describe the new query rewrite features implemented in PolarDB for MySQL.

• Column pruning for derived tables and views

• Redundant DISTINCT elimination

• OR/IN to UNION ALL transformation

• IN to JOIN transformation

• Subquery optimization

• Subquery folding

• Subquery decorrelation

• Join elimination

• LEFT JOIN elimination

• Cost-based query transformation

• Join condition pushdown

• Condition pushdown:

  • HAVING vs. WHERE

  • WHERE condition pushdown to derived tables

  • WHERE clause pushdown to IN subqueries

  • Join condition pushdown to materialized derived tables

  • Transitive predicate generation (enhanced condition pushdown)