Description:
Optimization for IN seems to be broken in 4.0.15 - this query is optimized just fine in MySQL 4.0.14 and MySQL 3.23:
MySQL 4.0.15:
mysql> explain select count(*) from rn where c in ("1");
+-------+-------+---------------+------+---------+------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+--------------------------+
| rn | index | NULL | c | 21 | NULL | 38 | Using where; Using index |
+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
MySQL 3.23.58:
mysql> explain select count(*) from rn where c in (1);
+-------+-------+---------------+------+---------+------+------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+-------------------------+
| rn | range | c | c | 5 | NULL | 1 | where used; Using index |
+-------+-------+---------------+------+---------+------+------+-------------------------+
1 row in set (0.04 sec)
This query works just fine:
explain select count(*) from rn where c=1;
How to repeat:
CREATE TABLE rn (
c int(11) default NULL,
KEY c (c)
) TYPE=MyISAM;
INSERT INTO rn VALUES (1),(2),(2),(2),(2),(2),(2),(2),(3),(3),(3),(3),(3),(3),(3),(3),(3),(4),(4),(4),(4),(4),(4),(4),(4),(4),(4),(5),(5),(5),(5),(5),(5),(5),(5),(5),(5),(5);
explain select count(*) from rn where c in (1);
Description: Optimization for IN seems to be broken in 4.0.15 - this query is optimized just fine in MySQL 4.0.14 and MySQL 3.23: MySQL 4.0.15: mysql> explain select count(*) from rn where c in ("1"); +-------+-------+---------------+------+---------+------+------+--------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+------+---------+------+------+--------------------------+ | rn | index | NULL | c | 21 | NULL | 38 | Using where; Using index | +-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) MySQL 3.23.58: mysql> explain select count(*) from rn where c in (1); +-------+-------+---------------+------+---------+------+------+-------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+------+---------+------+------+-------------------------+ | rn | range | c | c | 5 | NULL | 1 | where used; Using index | +-------+-------+---------------+------+---------+------+------+-------------------------+ 1 row in set (0.04 sec) This query works just fine: explain select count(*) from rn where c=1; How to repeat: CREATE TABLE rn ( c int(11) default NULL, KEY c (c) ) TYPE=MyISAM; INSERT INTO rn VALUES (1),(2),(2),(2),(2),(2),(2),(2),(3),(3),(3),(3),(3),(3),(3),(3),(3),(4),(4),(4),(4),(4),(4),(4),(4),(4),(4),(5),(5),(5),(5),(5),(5),(5),(5),(5),(5),(5); explain select count(*) from rn where c in (1);