> 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.md).

# Subquery Optimizations

{% columns %}
{% column %}
{% content-ref url="/pages/abmyfasD7X2YsEuIWRch" %}
[Condition Pushdown Into IN subqueries](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/condition-pushdown-into-in-subqueries.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Describes Condition Pushdown into IN subqueries, enabled through the condition\_pushdown\_for\_subquery optimizer\_switch flag.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/5QNkSK53QskSU5IYcaOt" %}
[Conversion of Big IN Predicates Into Subqueries](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/conversion-of-big-in-predicates-into-subqueries.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Converts large IN predicates into IN subqueries once the list exceeds in\_predicate\_conversion\_threshold elements, avoiding costly range analysis.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/qOLJUCkXtu0aQZnva4oC" %}
[EXISTS-to-IN Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/exists-to-in-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
EXISTS-to-IN rewrites trivially correlated and semi-join EXISTS subqueries into IN subqueries so MariaDB's richer IN-subquery strategies can apply.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/8hKnRyIvroc0LLqEScIj" %}
[Non-semi-join Subquery Optimizations](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/non-semi-join-subquery-optimizations.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Covers strategies for IN-subqueries that cannot become semi-joins, chiefly materialization (with NULL-aware partial matching) and the IN-to-EXISTS transformation.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/ngGFOQUclcsGAptbtOQK" %}
[Optimizing GROUP BY and DISTINCT Clauses in Subqueries](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/optimizing-group-by.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Automatically removes redundant DISTINCT and GROUP BY-without-HAVING clauses in IN/ALL/ANY/SOME/EXISTS subqueries, allowing more efficient query plans.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/EaikBLrnprknxp6D8SJS" %}
[Semi-join Subquery Optimizations](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/semi-join-subquery-optimizations.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Explains semi-join IN-subqueries and MariaDB's five semi-join execution strategies: table pullout, FirstMatch, Materialization, LooseScan, and DuplicateWeedout.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/aCWgeNKHsbXzrq2DQYvL" %}
[Subquery Cache](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/subquery-cache.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
The subquery cache speeds up correlated subqueries by caching results with their correlation parameters, avoiding re-execution when a result is already cached.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/QDSmc43ypRvLzIyP8riC" %}
[Subquery Optimizations Map](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/subquery-optimizations-map.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Presents a map of the subquery types allowed in SQL and the optimizer strategies MariaDB provides to handle each, with links to individual optimization pages.
{% endcolumn %}
{% endcolumns %}

{% columns %}
{% column %}
{% content-ref url="/pages/zjE68nR3wjXIBN4NT0l3" %}
[Table Pullout Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/table-pullout-optimization.md)
{% endcontent-ref %}
{% endcolumn %}

{% column %}
Table pullout rewrites a semi-join subquery as a join by pulling tables out into the parent SELECT based on UNIQUE or PRIMARY key definitions.
{% endcolumn %}
{% endcolumns %}