Conversion of IN Predicates Into Subqueries

Scenarios

In MySQL Community Edition, if column IN (const1, const2, ...) is executed and there is an index on the column, the optimizer usually performs a range scan. If there are many elements in the IN list and the used memory exceeds the maximum memory defined by range_optimizer_max_mem_size, the range scan will fail and the query performance will deteriorate.

To solve this problem, there are several common approaches, each with its own limitations:

• You can increase the maximum memory that can be used. However, the memory is at the session level. It means that each session occupies the same memory, so the instance may be out of memory.
• If the range optimizer can be used but the number of elements in the IN list exceeds the eq_range_index_dive_limit value, index statistics are used instead of index dives. This may cause inaccurate estimation and performance rollback.

TaurusDB supports converting IN predicates into subqueries. For complex queries that meet the requirements in Prerequisites, the optimizer can convert large IN predicates as IN subqueries and may further transform them into semi-joins to enhance performance.

Conversion Process

Query before conversion:

select ... from lineitem where l_partkey in (...)

Query after conversion:

select ... from lineitem where l_partkey in 
 (select tb._col_1 from (values (9628136),(19958441),...) tb)

Prerequisites

• The kernel version of your TaurusDB instance must be 2.0.42.230600 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
• The number of elements in the IN list exceeds the value of rds_in_predicate_conversion_threshold.

Supported Query Statements

• SELECT
• INSERT ... SELECT
• REPLACE ... SELECT
• PREPARED STMT and views

Constraints

• Only the constant IN LIST (including statements that do not involve table queries, such as NOW()) is supported.
• Stored procedures, functions, and triggers are not supported.
• NOT IN is not supported. Statements where indexes cannot be used are not supported.

How to Use

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

Examples

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)

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)

set rds_in_predicate_conversion_threshold=3;
Query OK, 0 rows affected (0.00 sec)

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)

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

Performance Tests

sysbench is used to perform a benchmark test.

1. Prepare 10 million data records.

   sysbench /usr/share/sysbench/oltp_read_only.lua --tables=1 --report-interval=10 --table-size=10000000  --mysql-user=root --mysql-password=123456 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sbtest --time=300 --max-requests=0  --threads=200   prepare

2. Run a statement where there are 10,000 elements in the IN list.

   select count(*) from sbtest1 where id/k in (... ...);

The following table lists the performance comparison.

Figure 1 Time comparison