> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/subquery-optimizations-map.md).

# 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)

![](/files/3bLBU15Y3fZGHLTfIMqZ)

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](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/non-semi-join-subquery-optimizations.md#the-in-to-exists-transformation)
* [Subquery Caching](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/subquery-cache.md)
* [Semi-join optimizations](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/semi-join-subquery-optimizations.md)
  * [Table pullout](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/table-pullout-optimization.md)
  * [FirstMatch](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/firstmatch-strategy.md)
  * [Materialization, +scan, +lookup](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/semi-join-materialization-strategy.md)
  * [LooseScan](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/loosescan-strategy.md)
  * [DuplicateWeedout execution strategy](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimization-strategies/duplicateweedout-strategy.md)
* Non-semi-join [Materialization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/non-semi-join-subquery-optimizations.md#materialization-for-non-correlated-in-subqueries) (including NULL-aware and partial matching)
* Derived table optimizations
  * [Derived table merge](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables/derived-table-merge-optimization.md)
  * [Derived table with keys](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/optimizations-for-derived-tables/derived-table-with-key-optimization.md)

## See also

* [Subquery optimizations in MariaDB 5.3](/docs/release-notes/community-server/old-releases/5.3/changes-improvements-in-mariadb-5-3.md#subquery-optimizations)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}