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)
Non-semi-join Materialization (including NULL-aware and partial matching)
Derived table optimizations
This page is licensed: CC BY-SA / Gnu FDL