Bug #101459 With more than one value in IN() clause, query skip index use.
Submitted: 4 Nov 2020 13:05 Modified: 5 Nov 2020 11:56
Reporter: lalit Choudhary Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.6.50,5.7.32 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[4 Nov 2020 13:05] lalit Choudhary
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.
[5 Nov 2020 11:56] MySQL Verification Team
Hello Lalit,

Thank you for the report and feedback!

Thanks,
Umesh