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

_{This page is licensed: CC BY-SA / Gnu FDL}