How MySQL 8.0 Optimizer Transforms Complex Subqueries and Joins

Background and Architecture

In the previous article we covered basic SQL elements (tables, columns, functions, aggregates, grouping, ordering) for MySQL 8.0.25. This continuation focuses on more complex transformations such as subqueries, partitioned tables, and JOINs.

Transformation

remove_redundant_subquery_clause – permanently drop redundant subquery parts when the query involves a view and is being optimized.

remove_base_options – eliminate SELECT_DISTINCT when it can be skipped.

resolve_subquery – early unconditional subquery transformations, including converting subquery predicates to semi‑joins, materialization, IN→EXISTS, ALL/ANY→MIN/MAX, and scalar‑context substitution.

transform_scalar_subqueries_to_join_with_derived – turn eligible scalar subqueries into derived tables.

flatten_subqueries – convert semi‑join subqueries into nested JOINs (performed once per query lifetime).

apply_local_transforms – a collection of local optimizations applied bottom‑up after flattening.

Detailed Transformation Process

1 Resolve Subquery (resolve_subquery)

Parse statements containing subqueries, mark candidates for semi‑join conversion, check optimizer switches, ensure no GROUP BY, HAVING, WINDOW functions, and verify that both outer and inner query blocks have at least one table and are not straight joins.

Mark subquery for materialization when semi‑join is not possible.

Handle IN/ANY/EXISTS predicates, decide between semi‑join, antijoin, or materialization.

2 Scalar Subquery → Derived Table

This feature, introduced in 8.0.16, improves support for secondary engines by converting scalar subqueries into derived tables. Example before/after execution plans are shown.

Collect scalar subqueries from JOIN, WHERE, HAVING, and SELECT list.

Identify grouped scalar subqueries that can become derived tables.

Create Query_expression/Query_block for the derived table, move aggregate items, and adjust references.

Replace original scalar subquery expressions with columns from the derived table.

3 Flatten Subqueries (flatten_subqueries)

Transform semi‑join subqueries into nested JOINs (irreversible). Prioritization rules: dependent subqueries first, more inner tables first, earlier position first.

4 Apply Local Transforms (apply_local_transforms)

delete_unused_merged_columns – remove unnecessary columns after derived tables are eliminated.

simplify_joins – flatten nested joins into a single join list.

prune_partitions – static partition pruning based on constant predicates.

5 Push Conditions to Derived Tables (push_conditions_to_derived_tables)

Pushes WHERE conditions into materialized derived tables when possible, skipping tables with UNION, LIMIT, outer‑join inner tables, or CTE‑derived tables.

set optimizer_switch='derived_merge=off';
EXPLAIN FORMAT=tree SELECT * FROM (SELECT c1,c2 FROM t1) dt WHERE c1>10;

Summary

The two articles together illustrate rule‑based optimizer transformations in MySQL, covering subquery conversion, semi‑join handling, derived‑table materialization, flattening, join simplification, partition pruning, and condition push‑down. While rule‑based changes can dramatically speed up execution, some cases still require cost‑based decisions or are not yet supported.

References

MySQL 8.0 Server层最新架构详解

WL#13520: Transform correlated scalar subqueries

WL#8084 – Condition pushdown to materialized derived table

WL#2980: Subquery optimization: Semijoin

WL#3740, WL#3741, WL#3750, WL#3751 – Various semijoin strategies

WL#4389: Make IN optimizations also handle EXISTS

WL#4245: Transform NOT EXISTS and NOT IN to anti‑join

WL#2985: Perform Partition Pruning of Range conditions

MySQL source‑code analyses on semi‑join and subquery optimizations

Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions