how to transform in predicates to joins - PolarDB - Alibaba Cloud Documentation Center

PolarDB supports transforming IN predicates to JOINs. For complex queries that meet the prerequisites, the optimizer converts large IN predicates into JOINs to improve query performance.

Prerequisites

The cluster must be running PolarDB for MySQL 8.0, revision version 8.0.2.2.10 or later. For more information, see Query the version number.
The number of elements in the IN list exceeds the value of the loose_in_predicate_conversion_threshold parameter.
The [NOT] IN condition is at the top layer of a WHERE or ON clause.

Usage

You can use the loose_in_predicate_conversion_threshold parameter to configure the feature that transforms IN predicates into JOINs. For more information, see Set cluster and node parameters.

Parameter Name

Level

Description

loose_in_predicate_conversion_threshold

Global

Controls the feature that transforms IN predicates to JOINs.

If the number of elements in the IN list of an SQL statement is greater than or equal to the value of this parameter, the optimizer transforms the IN predicate into a JOIN. Valid values: 0 to 18446744073709551615. Default value: 5000.

Note

If you set this parameter to 0, this feature is disabled.

Examples

Original query:

mysql> EXPLAIN  SELECT * FROM t WHERE a IN (1,2,3,5,5);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  160 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN format=tree SELECT * FROM t WHERE a IN (1,2,3,5,5);
+------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                              |
+------------------------------------------------------------------------------------------------------+
| -> Filter: (t.a IN (1,2,3,5,5))  (cost=16.25 rows=80)
    -> TABLE scan ON t  (cost=16.25 rows=160)
 |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Transformed query:

mysql> SET in_predicate_conversion_threshold=5;
mysql> EXPLAIN  SELECT * FROM t WHERE a IN (1,2,3,5,5);
+----+-------------+------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys       | key                 | key_len | ref      | rows | filtered | Extra                    |
+----+-------------+------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+--------------------------+
|  1 | PRIMARY     | t          | NULL       | ALL    | NULL                | NULL                | NULL    | NULL     |  160 |   100.00 | Using where              |
|  1 | PRIMARY     | <derived3> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 8       | test.t.a |    1 |   100.00 | Using where; Using index |
|  3 | DERIVED     | NULL       | NULL       | NULL   | NULL                | NULL                | NULL    | NULL     | NULL |     NULL | IN-list Converted        |
+----+-------------+------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+--------------------------+
mysql> EXPLAIN format=tree SELECT * FROM t1 WHERE a IN (1,2,3,5,5);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop semijoin
    -> Filter: (t1.a IS NOT NULL)  (cost=0.55 rows=3)
        -> TABLE scan ON t1  (cost=0.55 rows=3)
    -> Filter: (t1.a = tvc_0._col_1)
        -> Index lookup ON tvc_0 using <auto_key0> (_col_1=t1.a)
            -> Materialize
                -> scan ON in-list: 5 rows