MySQL Forge

Resources for the MySQL Community

Categories: Optimizer | Subquery Optimizations

Subquery Works

Contents 1 Mostly completed work 2 Work in progress 2.1 Not currently promised for 6.0 but in development 3 Future work

For a user-friendly overview, see New Subquery Optimizations in MySQL 6.0 slides from MySQL User Conference 2008.

[edit] Mostly completed work

Current MySQL 6.0 includes implementations of the following optimizations:

• WL#2890 "Subquery optimization: Semijoin"
  • WL#3740 "Subquery optimization: Semijoin: Pull-out of inner tables"
  • WL#3741 "Subquery optimization: Semijoin: Duplicate elimination strategy"
  • WL#3750 "Subquery optimization: Semijoin: First-match strategy"
  • WL#3751 "Subquery optimization: Semijoin: Inside-out strategy"
• WL#1110 "Subquery optimization: Materialization"
• WL#3985 "Subquery optimization: smart choice between semi-join and materialization"

For a short description of the above optimizations, see 6.0 Subquery Optimization Cheatsheet. The code is in alpha stage (there are known bugs) but you can already try out these optimizations.

Preliminary results of survey of the impact on performance are at 6.0_Subquery_Optimization_Benchmarks.

[edit] Work in progress

This is what we're already doing or will definitely be doing in a short-term future:

• Timour: WL#3830: "Subquery optimization: Materialization: Partial matching of tuples with NULL components"
  • In a nutshell, this is making materialization to handle all cases with NULLable subquery arguments and NULL-aware results.

• Fix EXPLAIN
  • SergeyP, Igor, Timour: Discuss if/how we should change EXPLAIN for materialization strategy
    • want to see the word "materialization" there. Maybe we should replace "SUBQUERY" with "MATERIALIZE" or "MATERIALIZATION"?

• Single-table UPDATE with a semi-join-ok subquery in the WHERE clause: check if we handle those correctly and if they are optimized.

• (ongoing) Perform benchmarks and analysis

[edit] Not currently promised for 6.0 but in development

• Evgen Potemkin: WL#3485 "Subquery optimization: FROM (SELECT)"

• Roy Lyseng: WL#4389 "Subquery optimizations: Make IN optimizations also handle EXISTS"

[edit] Future work

Tasks that we intend to do but don't have any commitments yet

• WL#3341 "Subquery optimization: Shortcut the evaluation as soon as there is a match"
• WL#1117 "Subquery optimization: Avoid recalculating subquery if external fields have not changed"
• WL#4245 "Subquery optimization: FirstMatch strategy for anti-semi-join"

.