Description:
Here's the technology stack I'm using, Java 11, JOOQ 3.12.1 and MYSQL 8.0.27 ( upgraded from 5.7.28 ) with connection pooling(30) and engine InnoDB.
Here's the example query I've successfully executed with use of JOOQ within 1s in MySQL 5.7.28. IN query takes about 60k IDs. Now MYSQL 8.0.28 takes around 92s for this query.
select `id`, `name`, `description` from `ex_table` where `id` in (?,?,?,?,?,....);
Just for the information I've used mariadb and mysql connector both so I can conclude that there isn't the issue with the connector.
REFERENCE
show create table ex_table;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ex_table | CREATE TABLE `ex_table` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` varchar(1000) DEFAULT NULL,
`stack_id` mediumint unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unq_node` (`name`,`stack_id`),
KEY `idx_stack` (`stack_id`)
) ENGINE=InnoDB AUTO_INCREMENT=72279 DEFAULT CHARSET=utf8mb3 |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
SHOW TABLE STATUS WHERE name LIKE 'ex_table';
+------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| ex_table | InnoDB | 10 | Dynamic | 60272 | 43 | 2637824 | 0 | 5275648 | 4194304 | 72279 | 2022-06-30 07:10:14 | NULL | NULL | utf8_general_ci | NULL | | |
+------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.02 sec)
explain select `id`, `name`, `description`, `stack_id` from `ex_table` where `id` in (1,2,3,4,5,6,7,8,9,10);
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | ex_table | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
Running Full Query with Explain:
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | ex_table | NULL | ALL | PRIMARY | NULL | NULL | NULL | 60272 | 50.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 2 warnings (0.51 sec)
How to repeat:
LONG PARAMETERIZED QUERIES with IN condition
