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
- Subqueries that have form
- 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, andEXISTS (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:
- Non-semi-join Materialization (including NULL-aware and partial matching)
- Derived table optimizations
See also
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.