Bug #80067 | Index on BIT column is NOT used when column name only is used in WHERE clause | ||
---|---|---|---|
Submitted: | 20 Jan 2016 10:50 | Modified: | 20 Jan 2016 11:39 |
Reporter: | Valeriy Kravchuk | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.6.28, 5.7.10 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | bit, missing manual, Optimizer |
[20 Jan 2016 10:50]
Valeriy Kravchuk
[20 Jan 2016 11:39]
MySQL Verification Team
Hello Valeriy, Thank you for the report and test case. Observed this with 5.6.28 and 5.7.10 builds. Thanks, Umesh
[20 Jan 2016 12:59]
Øystein Grøvlen
Hi Valeriy, I do not understand why you focus on BIT columns. INTEGER columns have same behavior: mysql> show create table t5; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t5 | CREATE TABLE `t5` ( `pk` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `idx` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec) mysql> explain select * from t5 force index(idx) where a; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t5 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 80.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0,00 sec) mysql> explain select * from t5 force index(idx) where a>0; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t5 | NULL | range | idx | idx | 5 | NULL | 5 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0,00 sec)
[15 Feb 2016 12:44]
Sveta Smirnova
Øystein, I believe worst case here is what this is true for BOOLEAN also.
[17 Jun 2020 23:13]
Mark Guinness
Also reproduced on MySQL 8.0, see fiddle at https://www.db-fiddle.com/f/cmDYMj5j2SL7uTjZikzBaY/1. These use the index: select id, c2 from tbit where c1 > 0; select id, c2 from tbit where c1 = TRUE; These ignore the index: select id, c2 from tbit where c1; select id, c2 from tbit where c1 IS TRUE; Note: The MariaDB documentation at https://mariadb.com/kb/en/is/ makes the following distinction for the IS operator. "There is an important difference between using IS TRUE or comparing a value with TRUE using =. When using =, only 1 equals to TRUE. But when using IS TRUE, all values which are logically true (like a number > 1) return TRUE."