Subquery optimizations map

Below is a map, that shows all kinds of subqueries allowed in the SQL language, and optimizer strategies availabe 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 ...) 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)

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

See also

• Subquery optimizations in MariaDB 5.3