Conversion of Big IN Predicates Into Subqueries - Source

Revision: 114348
User: Sergei Petrunia
Date: 2022-03-02 22:15

Starting from MariaDB 10.3, the optimizer converts certain big IN predicates into IN subqueries.

That is, an IN predicate in the form

<<sql>>
column [NOT] IN (const1, const2, .... )
<</sql>>

is converted into an equivalent IN-subquery:

<<sql>>
column [NOT] IN (select ... from temporary_table)
<</sql>>
which opens new opportunities for the query optimizer.

The conversion happens if the following conditions are met:

* the IN list has more than 1000 elements  (One can control it through the [[server-system-variables/#in_predicate_conversion_threshold|in_predicate_conversion_threshold]] parameter).
* the [NOT] IN condition is at the top level of the WHERE/ON clause.

== Controlling the Optimization
* The optimization is on by default. MariaDB 10.3.18 (and debug builds prior to that) introduced the [[server-system-variables/#in_predicate_conversion_threshold|in_predicate_conversion_threshold]] variable. Set to ##0## to disable the optimization.

== Benefits of the Optimization

If ##column## is a key-prefix, MariaDB optimizer will process the condition

<<sql>>
column [NOT] IN (const1, const2, .... )
<</sql>>

by trying to construct a range access. If the list is large, the analysis may take a lot of memory and CPU time. The problem gets worse when ##column## is a part of a multi-column index and the query has conditions on other parts of the index.

Conversion of IN predicates into a subqueries bypass the range analysis, which means the query optimization phase will use less CPU and memory.

Possible disadvantages of the conversion are are:
* The optimization may convert 'IN LIST elements' key accesses to a table scan (if there is no other usable index for the table)
* The estimates for the number of rows matching the ##IN (...)## are less precise.

== See Also
* [[in|IN operator]]
== Links
https://jira.mariadb.org/browse/MDEV-12176

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.

Conversion of Big IN Predicates Into Subqueries - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB