Description:
Performing IN clause with a few values chooses index which is a bad performance.
How to repeat:
CREATE TABLE `demoInClause` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint NOT NULL,
`col1` tinyint DEFAULT NULL,
`is_blacklist` tinyint(1) NOT NULL DEFAULT '0',
`is_disabled` tinyint DEFAULT '0',
PRIMARY KEY (`id`),
KEY `ix_is_disabled` (`is_disabled`),
KEY `ix_status_is_disabled_is_blacklist_col1` (`status`,`is_disabled`,`is_blacklist`,`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
explain select count(*)
from (select`g`.`id` from `demoInClause` as `g`
where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s;
explain select count(*)
from (select`g`.`id` from `demoInClause` as `g` force index (ix_status_is_disabled_is_blacklist_col1)
where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s;
select count(*)
from (select`g`.`id` from `demoInClause` as `g`
where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s;
select count(*)
from (select`g`.`id` from `demoInClause` as `g` force index (ix_status_is_disabled_is_blacklist_col1)
where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s;
update demoInClause set is_disabled=0;
update demoInClause set is_blacklist=0;
update demoInClause set col1=1;
update demoInClause set status=10 order by round(Id) limit 10000;
update demoInClause set status=20 order by round(Id) limit 10000;
update demoInClause set status=30 order by round(Id) limit 10000;
update demoInClause set status=40 order by round(Id) limit 20000;
update demoInClause set status=50 order by round(Id) limit 30000;
mysql> explain select count(*)
-> from (select`g`.`id` from `demoInClause` as `g`
-> where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s;
+----+-------------+-------+------------+------+--------------------------------------------------------+----------------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------------------------------------+----------------+---------+-------+-------+----------+-------------+
| 1 | SIMPLE | g | NULL | ref | ix_is_disabled,ix_status_is_disabled_is_blacklist_col1 | ix_is_disabled | 2 | const | 50232 | 1.67 | Using where |
+----+-------------+-------+------------+------+--------------------------------------------------------+----------------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select count(*)
-> from (select`g`.`id` from `demoInClause` as `g` force index (ix_status_is_disabled_is_blacklist_col1)
-> where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s;
+----+-------------+-------+------------+-------+-----------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | g | NULL | range | ix_status_is_disabled_is_blacklist_col1 | ix_status_is_disabled_is_blacklist_col1 | 6 | NULL | 58605 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select count(*)
-> from (select`g`.`id` from `demoInClause` as `g`
-> where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s;
+----------+
| count(*) |
+----------+
| 34651 |
+----------+
1 row in set (0.12 sec)
mysql> select count(*)
-> from (select`g`.`id` from `demoInClause` as `g` force index (ix_status_is_disabled_is_blacklist_col1)
-> where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s;
+----------+
| count(*) |
+----------+
| 34651 |
+----------+
1 row in set (0.02 sec)
Description: Performing IN clause with a few values chooses index which is a bad performance. How to repeat: CREATE TABLE `demoInClause` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `status` tinyint NOT NULL, `col1` tinyint DEFAULT NULL, `is_blacklist` tinyint(1) NOT NULL DEFAULT '0', `is_disabled` tinyint DEFAULT '0', PRIMARY KEY (`id`), KEY `ix_is_disabled` (`is_disabled`), KEY `ix_status_is_disabled_is_blacklist_col1` (`status`,`is_disabled`,`is_blacklist`,`col1`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci explain select count(*) from (select`g`.`id` from `demoInClause` as `g` where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s; explain select count(*) from (select`g`.`id` from `demoInClause` as `g` force index (ix_status_is_disabled_is_blacklist_col1) where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s; select count(*) from (select`g`.`id` from `demoInClause` as `g` where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s; select count(*) from (select`g`.`id` from `demoInClause` as `g` force index (ix_status_is_disabled_is_blacklist_col1) where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s; update demoInClause set is_disabled=0; update demoInClause set is_blacklist=0; update demoInClause set col1=1; update demoInClause set status=10 order by round(Id) limit 10000; update demoInClause set status=20 order by round(Id) limit 10000; update demoInClause set status=30 order by round(Id) limit 10000; update demoInClause set status=40 order by round(Id) limit 20000; update demoInClause set status=50 order by round(Id) limit 30000; mysql> explain select count(*) -> from (select`g`.`id` from `demoInClause` as `g` -> where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s; +----+-------------+-------+------------+------+--------------------------------------------------------+----------------+---------+-------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+--------------------------------------------------------+----------------+---------+-------+-------+----------+-------------+ | 1 | SIMPLE | g | NULL | ref | ix_is_disabled,ix_status_is_disabled_is_blacklist_col1 | ix_is_disabled | 2 | const | 50232 | 1.67 | Using where | +----+-------------+-------+------------+------+--------------------------------------------------------+----------------+---------+-------+-------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select count(*) -> from (select`g`.`id` from `demoInClause` as `g` force index (ix_status_is_disabled_is_blacklist_col1) -> where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s; +----+-------------+-------+------------+-------+-----------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | g | NULL | range | ix_status_is_disabled_is_blacklist_col1 | ix_status_is_disabled_is_blacklist_col1 | 6 | NULL | 58605 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+-----------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) -> from (select`g`.`id` from `demoInClause` as `g` -> where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s; +----------+ | count(*) | +----------+ | 34651 | +----------+ 1 row in set (0.12 sec) mysql> select count(*) -> from (select`g`.`id` from `demoInClause` as `g` force index (ix_status_is_disabled_is_blacklist_col1) -> where `g`.`status` in (10, 20, 30, 40, 50) and `g`.`is_disabled` = 0 and `g`.`is_blacklist` = 0 and `g`.`col1` > 0) s; +----------+ | count(*) | +----------+ | 34651 | +----------+ 1 row in set (0.02 sec)