| Bug #62738 | Index is not used if I use IS TRUE instead of = TRUE when filtering | ||
|---|---|---|---|
| Submitted: | 14 Oct 2011 14:32 | Modified: | 16 Oct 2012 5:15 |
| Reporter: | Cyril SCETBON | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 5.1.60, 5.5.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | boolean, explain, INDEX | ||
[14 Oct 2011 14:46]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.60 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table t(id int, b boolean default FALSE);
Query OK, 0 rows affected (0.13 sec)
mysql> insert into t(id) select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t(id) select id from t;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t(id) select id from t;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t(id) select id from t;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into t(id) select id from t;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into t(id) select id from t;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> alter table t add index(b);
Query OK, 32 rows affected (0.18 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into t values(10,TRUE);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+------+
| id | b |
+------+------+
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 1 | 0 |
| 10 | 1 |
+------+------+
33 rows in set (0.00 sec)
mysql> explain select count(*) from t where b is true\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: b
key_len: 2
ref: NULL
rows: 33
Extra: Using where; Using index
1 row in set (0.03 sec)
mysql> explain select count(*) from t where b = true\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: b
key: b
key_len: 2
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
Index is used in both cases, but it is "ref" access for one row vs "index" (entire index scan).
[16 Oct 2012 5:15]
Erlend Dahl
Analysis from dev team: This would have been a valid and useful feature request if MySQL supported the data type boolean. However, when creating a table and specifying a boolean data type, the server interprets this as synonym for tinyint(1). According to the manual, "Nonzero values are considered true" for such data types, so it will not be possible for us to transform the expression "b IS TRUE" into "b = 1", because it will miss all values that are not zero and not 1.
Description: If I search for rows WHERE myboolean IS TRUE, MySQL does not use the index, while it uses it If I use WHERE myboolean = TRUE The optimizer should rewrite "WHERE myboolean IS TRUE" as "WHERE myboolean=1" as it does when I use "WHERE myboolean = TRUE" So, I got a difference when I search in a table with 75 millions of rows where only one row as the boolean set ! mysql> explain SELECT * FROM LgstApplication WHERE EEDNeedsRecalculation = TRUE\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: LgstApplication type: ref possible_keys: eedRecalculationIndex key: eedRecalculationIndex key_len: 1 ref: const rows: 1 Extra: 1 row in set (0.00 sec) mysql> explain SELECT * FROM LgstApplication WHERE EEDNeedsRecalculation IS TRUE\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: LgstApplication type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 73946933 Extra: Using where 1 row in set (0.00 sec) How to repeat: should be easy no ???? mysql> create table t(id int, b boolean default FALSE); Query OK, 0 rows affected (0.01 sec) mysql> insert into t(id) select 1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t; Query OK, 32 rows affected (0.00 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t; Query OK, 64 rows affected (0.00 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t union select id from t union select id from t; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t union all select id from t union all select id from t; Query OK, 387 rows affected (0.00 sec) Records: 387 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t union all select id from t union all select id from t; Query OK, 1548 rows affected (0.02 sec) Records: 1548 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t union all select id from t union all select id from t; Query OK, 6192 rows affected (0.03 sec) Records: 6192 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t union all select id from t union all select id from t; Query OK, 24768 rows affected (0.18 sec) Records: 24768 Duplicates: 0 Warnings: 0 mysql> insert into t(id) select id from t union all select id from t union all select id from t; Query OK, 99072 rows affected (0.43 sec) Records: 99072 Duplicates: 0 Warnings: 0 mysql> alter table t add index(b); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t values(10,TRUE); Query OK, 1 row affected (0.00 sec) mysql> explain select count(*) from t where b is true\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: index possible_keys: NULL key: b key_len: 2 ref: NULL rows: 131783 Extra: Using where; Using index 1 row in set (0.00 sec) mysql> explain select count(*) from t where b = true\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: b key: b key_len: 2 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec) Suggested fix: the fastest patch would be to rewrite "IS TRUE" as "= 1" like it is with "= TRUE" mysql> explain extended select count(*) from t where b is true\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: index possible_keys: NULL key: b key_len: 2 ref: NULL rows: 131783 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings \G *************************** 1. row *************************** Level: Note Code: 1003 Message: select count(0) AS `count(*)` from `jrep_data_qalperf`.`t` where (`jrep_data_qalperf`.`t`.`b` is true) 1 row in set (0.00 sec) mysql> explain extended select count(*) from t where b = true\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: b key: b key_len: 2 ref: const rows: 1 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings \G *************************** 1. row *************************** Level: Note Code: 1003 Message: select count(0) AS `count(*)` from `jrep_data_qalperf`.`t` where (`jrep_data_qalperf`.`t`.`b` = 1) 1 row in set (0.00 sec)