Optimize subqueries in MariaDB Server for improved performance. This section provides techniques and best practices to ensure your nested queries execute efficiently and enhance overall query speed.
Describes Condition Pushdown into IN subqueries, enabled through the condition_pushdown_for_subquery optimizer_switch flag.
Converts large IN predicates into IN subqueries once the list exceeds in_predicate_conversion_threshold elements, avoiding costly range analysis.
EXISTS-to-IN rewrites trivially correlated and semi-join EXISTS subqueries into IN subqueries so MariaDB's richer IN-subquery strategies can apply.
Covers strategies for IN-subqueries that cannot become semi-joins, chiefly materialization (with NULL-aware partial matching) and the IN-to-EXISTS transformation.
Automatically removes redundant DISTINCT and GROUP BY-without-HAVING clauses in IN/ALL/ANY/SOME/EXISTS subqueries, allowing more efficient query plans.
Explains semi-join IN-subqueries and MariaDB's five semi-join execution strategies: table pullout, FirstMatch, Materialization, LooseScan, and DuplicateWeedout.
The subquery cache speeds up correlated subqueries by caching results with their correlation parameters, avoiding re-execution when a result is already cached.
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.
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.
Last updated
Was this helpful?
Was this helpful?
