Updated on 2024-09-06 GMT+08:00

Usage

You can use the rds_in_predicate_conversion_threshold parameter to convert IN predicates into subqueries.

The default value is 0, indicating the conversion is disabled. To configure this parameter, contact customer service.

Table 1 Parameter description

Parameter

Level

Description

rds_in_predicate_conversion_threshold

Global

Controls the minimum number of elements in the value list of an IN predicate that triggers its conversion to an IN subquery.

Example:

  • Query before conversion:
    mysql> explain select * from t where a in (1,2,3,4,5);
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | t     | NULL       | ALL  | idx1          | NULL | NULL    | NULL |    5 |   100.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,4,5);
    +-------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                         |
    +-------------------------------------------------------------------------------------------------+
    | -> Filter: (t.a in (1,2,3,4,5))  (cost=0.75 rows=5)
        -> Table scan on t  (cost=0.75 rows=5)
     |
    +-------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

  • Query after conversion:
    mysql> set rds_in_predicate_conversion_threshold=3;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> explain select * from t where a in (1,2,3,4,5);
    +----+-------------+------------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+-------------------+
    | id | select_type | table            | partitions | type   | possible_keys       | key                 | key_len | ref      | rows | filtered | Extra             |
    +----+-------------+------------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+-------------------+
    |  1 | SIMPLE      | t                | NULL       | ALL    | idx1                | NULL                | NULL    | NULL     |    5 |   100.00 | Using where       |
    |  1 | SIMPLE      | <in_predicate_2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5       | test.t.a |    1 |   100.00 | IN-list converted |
    +----+-------------+------------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+-------------------+
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> explain format=tree select * from t where a in (1,2,3,4,5);
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                                     |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | -> Nested loop inner join  (cost=2.50 rows=5)
        -> Filter: (t.a is not null)  (cost=0.75 rows=5)
            -> Table scan on t  (cost=0.75 rows=5)
        -> Single-row index lookup on <in_predicate_2> using <auto_distinct_key> (a=t.a)  (cost=0.27 rows=1)
     |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    EXPLAIN returns the execution plan. There is <in_predicate_*> (* indicates a number) in the table column. It means that the table is a temporary table that stores all data in the IN query.

    You can also view in_to_subquery_conversion information in the optimize trace.

    | explain format=tree select * from t where a in (1,2,3,4,5) | {
      "steps": [
        {
          "join_preparation": {
            "select#": 1,
            "steps": [
              {
                "IN_uses_bisection": true
              },
              {
                "in_to_subquery_conversion": {
                  "item": "(`t`.`a` in (1,2,3,4,5))",
                  "steps": [
                    {
                      "creating_tmp_table": {
                        "tmp_table_info": {
                          "table": "intermediate_tmp_table",
                          "columns": 1,
                          "row_length": 5,
                          "key_length": 5,
                          "unique_constraint": false,
                          "makes_grouped_rows": false,
                          "cannot_insert_duplicates": true,
                          "location": "TempTable"
                        }
                      }
                    },