Item_in_subselect/Item_allany_subselect::select_transformer->select_in_like_transformer.For simple scalar subqueries, replace with the execution result in the query.Item_singlerow_subselect::select_transformer.If the two strategies cannot be used above, select transformer according to type:

Set Subquery_strategy::CANDIDATE_FOR_DERIVED_TABLE and add sj_candidates.

This subquery cannot be transformed to derived table (m_subquery_to_derived_is_impossible).

If the left parameter is not multi-column subquery, it is not supported.

The parent query also has at least one table, and LEFT JOIN operation can be performed.

Parent query blocks can support semijoins.

Subquery predicate is in the WHERE clause (currently not implemented in the ON clause) and is the expression tree of ANDs or ORs.

If it is EXISTS, there must be no aggregation.

Subquery is IN/=ANY or EXISTS predicate.

Optimizer enables subquery_to_derived=on.

If it is impossible to transform to semijoin, try materialization method and transform judgment conditions.

Mark whether subquery adopts the materialization scheme during execution. Set Subquery_strategy::CANDIDATE_FOR_SEMIJOIN and add sj_candidates.offset starts from the first line, and limit is not 0. offset and limit are valid for semjoin.Determine whether the result needs to be transformed to true or false, whether it is NULL, and whether antijoin or semijoin operation can be performed.The return value of the IN predicate is certain, not RAND.The parent query block does not prohibit semijoin.Neither parent query nor subquery can have straight join.The parent query also has at least one table.The strategy of the subquery is not specified (Subquery_strategy::UNSPECIFIED).At least one table is needed, rather than using 'SELECT 1'.External query blocks can support semijoins.The resolution phase involves Query_block::RESOLVE_CONDITION and Query_block::RESOLVE_JOIN_NEST and does not use the latest Hyper optimizer.Subquery has no HAVING or WINDOW functions.Subquery does not have implicit and explicit GROUP BY.Subquery is simple query block instead of UNION.Subquery is predicate of IN/=ANY and EXIST subquery.Ensure there are no restrictions on OPTIMIZER_SWITCH_SEMIJOIN and HINT.Statements with subqueries in resolution conditions require some early-stage unlimited subquery transformations, including: Detailed Transformation Process 2.1 Resolve Subquery (resolve_subquery) Pushing conditions down to derived tables must be done after validity checks of grouped queries are done by apply_local_transforms().Įliminate unused objects like window definitions and redundant sorts.

prune_partitions: Perform partition pruning for a given table and condition.

simplify_joins: Convert all outer joins to inner joins if possible.

delete_unused_merged_columns: If query block contains one or more merged derived tables/views, walk through lists of columns in select lists and remove unused columns.

This transformation is performed once in query lifetime and is irreversible. Convert candidate subquery predicates into semi-join join nests. Transform eligible scalar subqueries to derived tablesĬonvert semi-join subquery predicates into semi-join join nests.

transform_scalar_subqueries_to_join_with_derived:.

Substitute trivial scalar-context subquery with its value.

Perform more/less ALL/ANY -> MIN/MAX rewrite.

Mark the subquery for execution using materialization.

Convert subquery predicate into semi-join.

Resolve_subquery: Resolve predicate involving subquery and perform early unconditional subquery transformations using one of the options below: Remove SELECT_DISTINCT options from a query block if it can skip distinct. Removal should take place when a query involving a view is optimized, not when the view is created.

Permanently remove redundant parts from the query if: The outline is listed below: Transformation This article introduces more complex transformation process of subqueries, partitioned tables, and JOIN operations. In the Query Resolution of MySQL 8.0 Optimizer article, we focused on the resolving, setting, and transformation processes of SQL basic elements in MySQL 8.0.25, including tables, columns, functions, aggregation, grouping, sorting, etc.

Mysql optimizer cost model