Setting the IN List Join Threshold
The inListJoinThreshold flag sets the number of values above which an IN list is compiled as an inner join
against the listed values, instead of being expanded into a chain of equality comparisons.
Large IN lists compile faster as a join, because a join is a single hash lookup while the comparison chain grows
with both the number of values and the number of rows. Lowering the threshold applies the join form to smaller lists,
and raising it keeps the comparison form for larger ones.
The following describes the inListJoinThreshold flag:
Data type - int
Default value -
100Allowed values - Any positive integer
A multi-column IN list such as (a, b) IN ((1, 2), (3, 4), ...) is also compiled as a join, against the listed
rows. For such a list the threshold counts comparisons, one per column in each row, so a list comparing two columns is
compiled as a join once it has more rows than half the threshold, which is more than 50 rows by default. See IN Subqueries and Multi-Column IN.
The join form applies to an IN list used as a filter condition, where a value that does not match drops the
row. An IN used elsewhere in a statement keeps the comparison form regardless of this flag, as do lists of
ARRAY, BLOB, INTERVAL, and DATETIME2 values, which cannot serve as a join key.
The flag may be set for the session:
SET inListJoinThreshold = 50;