MySQL Forge

Resources for the MySQL Community

Categories: Optimizer | Subquery Optimizations

Subquery Works

Contents 1 Mostly completed work 2 Work in progress 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

MySQL 6.0 (6.0.3 in particular) already 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"

For a short description of the above optimizations, see 6.0 Subquery Optimization Cheatsheet.

The code is in alpha stage (there are known bugs) and you can already try out these optimizations in MySQL 6.0.3 and up.

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:

• SergeyP: WL#3985: "Subquery optimization: smart choice between semi-join and materialization"
  • ATM semj-join is always preferred over materialization while it can be slower in certain cases

• 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: Fix EXPLAIN for FirstMatch strategy.
  • 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"?

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

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

• Perform benchmarks and analysis
  • SergeyP: make an initial assessment of the impact of new optimizations (done, see 6.0_Subquery_Optimization_Benchmarks)
  • Ranger: Go through bugs/support cases/etc, find and analyze subquery performance cases

[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"

.