Re: inner join on range criteria: unable to use index?

[Joseph Fisk]

Here's another example that doesn't involve a join:

CREATE TABLE `probeinfo` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`chr` varchar(30) default NULL,
`start` bigint(20) default NULL,
`stop` bigint(20) default NULL,
PRIMARY KEY (`id`),
KEY `start` (`start`,`stop`,`chr`)
) ENGINE=InnoDB

explain select * from probeinfo where chr='chr1' and start >= 935365 AND stop <=991355;

+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | probeinfo | ALL | start | NULL | NULL | NULL | 26461244 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+

The index appears in possible_keys but isn't actually used.

Using force index changes things slightly:

mysql> explain select * from probeinfo force index (start) where chr='chr1' and start >= 935365 AND stop <=991355;
+----+-------------+-----------+-------+---------------+-------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-------+---------+------+----------+-------------+
| 1 | SIMPLE | probeinfo | range | start | start | 18 | NULL | 13230622 | Using where |
+----+-------------+-----------+-------+---------------+-------+---------+------+----------+-------------+

Notice that 'rows' is still 13 million.

A bit more data: the table has 26 million rows, and the query would return 145 rows.