Below is a map showing all types of subqueries allowed in the SQL language, and
the optimizer strategies availabe to handle them.
* Uncolored areas represent different kinds of subqueries, for example:
** Subqueries that have form <<code>>x IN (SELECT ...)<</code>>
** Subqueries that are in the <<code>>FROM<</code>> 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, <<code>>x IN (SELECT ...)<</code>> queries are the most important,
and <<code>>EXISTS (SELECT ...)<</code>> 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)
{{subquery-optimizations-map2}}
== Links to pages about individual optimizations:
* [[non-semi-join-subquery-optimizations#the-in-to-exists-transformation|IN->EXISTS]]
* [[subquery-cache|Subquery Caching]]
* [[semi-join-subquery-optimizations|Semi-join optimizations]]
** [[table-pullout-optimization|Table pullout]]
** [[firstmatch-strategy|FirstMatch]]
** [[semi-join-materialization-strategy|Materialization, +scan, +lookup]]
** [[loosescan-strategy|LooseScan]]
** [[duplicateweedout-strategy|DuplicateWeedout execution strategy]]
* Non-semi-join [[non-semi-join-subquery-optimizations#materialization-for-non-correlated-in-subqueries|Materialization]] (including NULL-aware and partial matching)
* Derived table optimizations
** [[derived-table-merge-optimization|Derived table merge]]
** [[derived-table-with-key-optimization|Derived table with keys]]
== See also
* [[what-is-mariadb-53#subquery-optimizations|Subquery optimizations in MariaDB 5.3]]
