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)

subquery-map-2013

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)

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.