New Subquery Optimizations in MySQL 6.0

This talk gives an exhaustive coverage of subquery optimizations in MySQL. The introduction part describes subquery optimizations in MySQL 4.1/5.0. The main part covers new subquery optimizations in MySQL 6.0:

• Table pullout
• Duplicate elimination
• FirstMatch
• InsideOut scan
• Materialization

The description of each of the optimizations is accompanied by a demonstration of how the optimization speeds up execution of a real-world query and how it is reflected in the EXPLAIN output.

The talk ends with a description of our plans for future subquery optimization work.