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.

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"
                      }
                    }
                  },