All pages
Powered by GitBook
1 of 1

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)

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

  • Subquery Caching

  • Semi-join optimizations

    • Table pullout

    • FirstMatch

    • Materialization, +scan, +lookup

    • LooseScan

    • DuplicateWeedout execution strategy

  • Non-semi-join Materialization (including NULL-aware and partial matching)

  • Derived table optimizations

    • Derived table merge

    • Derived table with keys

See also

  • Subquery optimizations in MariaDB 5.3

This page is licensed: CC BY-SA / Gnu FDL