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)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.0.21-BK, 5.0.18
• OS: Linux (Linux)
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: qc

[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.