Categories: Optimizer | Subquery Optimizations

6.0 Subquery Optimization Cheatsheet

Contents


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:

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:

[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 */ ) 

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.

Retrieved from "http://forge.mysql.com/wiki/6.0_Subquery_Optimization_Cheatsheet"

This page has been accessed 2,446 times. This page was last modified 18:54, 20 April 2008.

Find

Browse
MySQLForge
Main Page
Current events
Recent changes
Random page
Help
Edit
Edit this page
Editing help
This page
Discuss this page
Post a comment
Printable version
Context
Page history
What links here
Related changes
My pages
Special pages
New pages
File list
Statistics
Bug reports
More...