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 (The threshold number is hard-coded in the release builds, in debug builds, 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

• There is no optimizer_switch flag for this optimization.
• Debug builds have @@in_predicate_conversion_threshold variable.

Links

https://jira.mariadb.org/browse/MDEV-12176