Bug #18520 Index V not used for 'where V' and 'where not V'
Submitted: 26 Mar 2006 13:26 Modified: 28 Jul 2006 3:26
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0.21-BK, 5.0.18 OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: qc
Triage: D5 (Feature request)

[26 Mar 2006 13:26] Olaf van der Spek
Description:
The optimizer is unable to use indexes for V or not V.
V should be equivalent to V != 0 and 
V should be equivalent to V = 0.

How to repeat:
CREATE TABLE `xcl_input` (
  `iid` int(11) NOT NULL auto_increment,
  `d` blob NOT NULL,
  `ipa` int(11) NOT NULL,
  `gid` int(11) NOT NULL default '0',
  `ws_gid` int(11) NOT NULL,
  `mtime` int(11) NOT NULL,
  PRIMARY KEY  (`iid`),
  KEY `ws_gid` (`ws_gid`)
);

mysql> explain select iid, d from xcl_input where ws_gid  order by iid limit 100;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | xcl_input | index | NULL          | PRIMARY | 4       | NULL | 285511 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select iid, d from xcl_input where ws_gid != 0 order by iid limit 100;
+----+-------------+-----------+-------+---------------+--------+---------+------+--------+-----------------------------+
| id | select_type | table     | type  | possible_keys | key    | key_len | ref  | rows   | Extra                       |
+----+-------------+-----------+-------+---------------+--------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | xcl_input | range | ws_gid        | ws_gid | 4       | NULL | 285367 | Using where; Using filesort |
+----+-------------+-----------+-------+---------------+--------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select iid, d from xcl_input where ws_gid = 0 order by iid limit 100; 
+----+-------------+-----------+------+---------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+----+-------------+-----------+------+---------------+--------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | xcl_input | ref  | ws_gid        | ws_gid | 4       | const |  145 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select iid, d from xcl_input where not ws_gid order by iid limit 100;    
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | xcl_input | index | NULL          | PRIMARY | 4       | NULL | 285511 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql>
[4 Apr 2006 15:40] Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 5.0.21-BK (ChangeSet@1.2134.1.1, 2006-04-04 11:50:54+02:00) on Linux.

To put some data into the table, I used:

insert into xcl_input (ws_gid) values (0);
insert into xcl_input (ws_gid) select ws_gid from xcl_input;
...
insert into xcl_input (ws_gid) select ws_gid from xcl_input;
insert into xcl_input (ws_gid) values (1);
[28 Jul 2006 3:26] Igor Babaev
This is definitely a feature request.
Let me to remind you that a construction of the form WHERE 'int_col' does not belong to the Standard SQL. Indeed MySQL interprets it as WHERE int_col != 0 but it does not mean that such a condition is involved in optimizations now.