MySQL Forge

Resources for the MySQL Community

Categories: Optimizer | Subquery Optimizations

6.0 Subquery Optimization Cheatsheet

Contents 1 Semi-join optimization 2 Materialization 3 Turning new optimizations on/off 4 Further reading

This page describes only what you can already try in the code. For the list of all subquery optimization tasks, including incomplete and planned items, see Subquery_Works.

[edit] Semi-join optimization

Semi-join handles only IN-subqueries that are AND-parts of the WHERE/ON clause:

 SELECT ... WHERE outer_expr IN (SELECT inner_expr FROM ... WHERE ... ) AND ... 

or

 SELECT ... WHERE (outer_expr1, ... outer_exprN) IN (SELECT inner_expr1, ... inner_exprN FROM ... WHERE ...) AND ... 

The subquery must satisfy these conditions:

• It is not a UNION or UNION ALL
• It doesn't have GROUP BY, HAVING or any kind of aggregate functions
• It doesn't have ORDER BY ... LIMIT clause

The subquery may or may not be correlated (new optimizations provide efficient handling for both kinds), it may also have DISTINCT or LIMIT clauses.

How semi-join is reflected in EXPLAIN:

• EXPLAIN EXTENDED + SHOW WARNINGS will show that the subquery was converted to semi-join (search for "semi join")
• Extra column content depend on the used strategy (and strategies can work together)
  • WL#3740: nothing special
  • WL#3741: Start temporary/ End temporary
  • WL#3750: FirstMatch
  • WL#3751: LooseScan

[edit] Materialization

Materialization handles all kinds of IN-subqueries that are uncorrelated:

 ... outer_expr [NOT] IN (SELECT inner_expr FROM /* some uncorrelated subquery */ ) ... 

or

 ...  (outer_expr1, ... outer_exprN) [NOT] IN (SELECT inner_expr1, ... inner_exprN FROM /* some uncorrelated subquery */ ) 

• The subselect must be uncorrelated
• One of the following must hold:
  • outer_expr and inner_expr cannot ever be NULL, or
  • it doesn't matter whether the result of subquery is FALSE or NULL (this is the case when the subuqery is not a NOT IN, and it is AND-part or OR-part of the WHERE clause).
• GROUP BY, aggregation, UNION, etc are allowed.
• The subquery may be located everywhere: it can be select list, be an argument to some function, etc.

At the moment, materialization doesn't stand out much in EXPLAIN output: you'll see select_type change from DEPENDENT SUBQUERY to SUBQUERY.

If a query can be run be executed with both materialization and semi-join, semi-join is always preferred (that will change before the release, see WL#3985).

[edit] Turning new optimizations on/off

The new @@optimizer_switch variable allows to turn one or both optimizations off. This command (note the lack of space after comma):

 mysql> set @@optimizer_switch='no_semijoin,no_materialization';

will make MySQL 6.0 handle subqueries in exactly the same way as it was done in MySQL 5.1. This command turns everything back on:

 mysql> set @@optimizer_switch='';

[edit] Further reading

This page is a short summary. For a full overview of 6.0 optimizations, see

1. New_Optimizer_Features_in_MySQL_6.0 MySQL University session, or
2. New Subquery Optimizations in MySQL 6.0 slides from MySQL User Conference 2008 (this is the most up to date source)

Also see Category:Subquery_Optimizations.