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 (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.
  • From MariaDB 10.3.18, this variable is exposed to the users and can be set in either release or debug builds. To disable this optimization, one can set in_predicate_conversion_threshold to 0.


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.