> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/subquery-optimizations/conversion-of-big-in-predicates-into-subqueries.md).

# Conversion of Big IN Predicates Into Subqueries

How the optimizer converts a long IN list into an IN subquery over a temporary table, and how in\_predicate\_conversion\_threshold controls it.

The optimizer converts certain big IN predicates into IN subqueries.

That is, an IN predicate in the form

```sql
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 at least as many values as [in\_predicate\_conversion\_threshold](/docs/server/server-management/variables-and-modes/server-system-variables.md#in_predicate_conversion_threshold) (1000 by default). For a list of rows, such as `(a, b) IN ((1, 2), (3, 4))`, each element of each row counts as a value, so 500 two-column rows reach the default threshold. See [Row Constructor Optimization](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/row-constructor-optimization.md).
* the \[NOT] IN condition is at the top level of the WHERE/ON clause.

## Controlling the Optimization

* The optimization is on by default. The [in\_predicate\_conversion\_threshold](/docs/server/server-management/variables-and-modes/server-system-variables.md#in_predicate_conversion_threshold) variable controls it. 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, .... )
```

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 subqueries bypass the range analysis, which means the query optimization phase will use less CPU and memory.

Possible disadvantages of the conversion 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 operator](/docs/server/reference/sql-structure/operators/comparison-operators/in.md)

## Links

[MDEV-12176](https://jira.mariadb.org/browse/MDEV-12176)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}