Conversion of Big IN Predicates Into Subqueries
You are viewing an old version of this article. View
the current version here.
Starting from MariaDB 10.3, the optimizer will convert certain big IN predicates into IN subqueries.
That is, an IN predicate in the form
column [NOT] IN (const1, const2, .... )
is converted into an equivalent IN-subquery:
column [NOT] IN (select ... from temporary_table)
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 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 in_predicate_conversion_threshold variable. Set to
0
to disable the optimization.
Reasons for why this optimization was added
If 'column' is a key-prefix, then MariaDB will build a tree of the values list check if it can use range access based on them, which if the list is large, will take a huge amount of memory and time.
The benefits of the optimization is:
- Less memory usage
- Faster optimizing stage (especially if there is any other comparisons with 'column')
The disadvantages are:
- The optimization may convert 'IN LIST elements' key accesses to a table scan (if there is no other usable index for the table)
See also
Links
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.