| Bug #101459 | With more than one value in IN() clause, query skip index use. | ||
|---|---|---|---|
| Submitted: | 4 Nov 2020 13:05 | Modified: | 12 Feb 2022 13:21 |
| Reporter: | lalit Choudhary | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 5.6.50,5.7.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[5 Nov 2020 11:56]
MySQL Verification Team
Hello Lalit, Thank you for the report and feedback! Thanks, Umesh
[12 Feb 2022 13:21]
lalit Choudhary
same issue with AND clause as well,
mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status='abc' and status='xyz' and priority != 100 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Impossible WHERE
1 row in set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status='abc' and priority != 100 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: inx_key1,inx_key2
key: inx_key2
key_len: 33
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) >
[28 Feb 2022 15:34]
Truls Bergskaug
Same for MySQL 5.7.37:
mysql> show global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.37 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.37-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
mysql> explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: inx_key1,inx_key2
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 80.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Description: Select Queries like in below example with more than one value in IN() clause, skip index use. Issue Reproduciable with 5.6.50 and 5.7.32, Same issue not reproduciable with 8.0.21 mysql > explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: inx_key1,inx_key2 key: NULL key_len: NULL ref: NULL rows: 1 Extra: Using where 1 row in set (0.01 sec) How to repeat: Issue Reproduciable with 5.6.50 and 5.7.32, Same issue not reproduciable with 8.0.21 Test case: CREATE TABLE `t1` (`id` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(10) DEFAULT 'new' , `priority` int(11) DEFAULT '300', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `inx_key1` (`status`,`priority`), KEY `inx_key2` (`status`,`updated_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; MySQL 5.6.50 mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: inx_key1,inx_key2 key: NULL key_len: NULL ref: NULL rows: 1 Extra: Using where 1 row in set (0.01 sec) mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc') and priority != 100 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ref possible_keys: inx_key1,inx_key2 key: inx_key2 key_len: 33 ref: const rows: 1 Extra: Using index condition; Using where 1 row in set (0.00 sec) MySQL 5.7.32 mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: inx_key1,inx_key2 key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.01 sec) mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc') and priority != 100 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: inx_key1,inx_key2 key: inx_key2 key_len: 33 ref: const rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) 1 row in set (0.00 sec) ******************** MySQL-8.0.21 works fine: mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc', 'xyz') and priority != 100 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: inx_key1,inx_key2 key: inx_key2 key_len: 33 ref: NULL rows: 2 filtered: 100.00 Extra: Using index condition; Using where 1 row in set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > explain select updated_at from t1 where status in ('abc') and priority != 100 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: inx_key1,inx_key2 key: inx_key2 key_len: 33 ref: const rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > exit Bye Suggested fix: It should use index regardless of the number of values specified in IN() clause.