Subquery Optimizations Map

Below is a map showing all types of subqueries allowed in the SQL language, and the optimizer strategies available to handle them.

• Uncolored areas represent different kinds of subqueries, for example:
  • Subqueries that have form x IN (SELECT ...)
  • Subqueries that are in the FROM clause
  • .. and so forth
• The size of each uncolored area roughly corresponds to how important (i.e. frequently used) that kind of subquery is. For example, x IN (SELECT ...) queries are the most important, and EXISTS (SELECT ...) are relatively unimportant.
• Colored areas represent optimizations/execution strategies that are applied to handle various kinds of subqueries.
• The color of optimization indicates which version of MySQL/MariaDB it was available in (see legend below)

Some things are not on the map:

• MariaDB doesn't evaluate expensive subqueries when doing optimization (this means, EXPLAIN is always fast). MySQL 5.6 has made a progress in this regard but its optimizer will still evaluate certain kinds of subqueries (for example, scalar-context subqueries used in range predicates)

Links to pages about individual optimizations:

• IN->EXISTS
• Subquery Caching

• Semi-join optimizations
  • Table pullout
  • FirstMatch
  • Materialization, +scan, +lookup
  • LooseScan
  • DuplicateWeedout execution strategy

• Non-semi-join Materialization (including NULL-aware and partial matching)

• Derived table optimizations
  • Derived table merge
  • Derived table with keys

See also

• Subquery optimizations in MariaDB 5.3