Description:
when you have an indexed column, which is also NOT NULL, and a query like this:
select * from table where indexed_column in (...);
Then index is used.
But with query like this:
select * from table where indexed_column in (...) or indexed_column IS NULL;
Then index is not used.
Feature request is just to eliminate this OR clause.
How to repeat:
CREATE TABLE t1 (
i_id int(11) NOT NULL default '0',
id tinyint(4) NOT NULL default '0',
UNIQUE KEY t1$i_id (i_id),
KEY t1$id (id)
) TYPE=MyISAM;
INSERT INTO t1 VALUES
(11347,27),(11348,50),(11349,2),(11307,37),(11308,37),(11309,27),(11310,
2),(11311,2),(11312,2),(11313,2),(11314,25),(11262,2),(11291,2),(11264,3
7),(11265,2),(11266,2),(11267,2),(11268,2),(11269,27),(11270,37),(11271,
27),(11272,27),(11273,2),(11274,27),(11275,27);
mysql> desc SELECT t1.i_id FROM t1 WHERE
t1.id IN (1,3) OR t1.id IS NULL LIMIT 100;
+-----------+------+---------------------+------+---------+------+------
+-------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-----------+------+---------------------+------+---------+------+------
+-------------+
| t1 | ALL | t1$id | NULL | NULL | NULL | 25
| Using where |
+-----------+------+---------------------+------+---------+------+------
+-------------+
1 row in set (0.00 sec)
mysql> desc SELECT t1.i_id FROM t1 WHERE
t1.id IN (1,3) LIMIT 100;
+-----------+-------+---------------------+---------------------+-------
--+------+------+-------------+
| table | type | possible_keys | key |
key_len | ref | rows | Extra |
+-----------+-------+---------------------+---------------------+-------
--+------+------+-------------+
| t1 | range | t1$id | t1$id |
1 | NULL | 2 | Using where |
+-----------+-------+---------------------+---------------------+-------
--+------+------+-------------+
1 row in set (0.00 sec)
