I'm doing a select on a table looking for rows that match between an upper and lower bound
SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
the ( simplified ) create table is:
CREATE TABLE `ranges` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`hi` varchar(15) DEFAULT NULL,
`hilong` bigint(20) DEFAULT NULL,
`lo` varchar(15) DEFAULT NULL,
`lolong` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `hilong` (`hilong`),
KEY `lolong` (`lolong`)
) ENGINE=InnoDB AUTO_INCREMENT=234447 DEFAULT CHARSET=utf8
explain shows that it's not using indexes:
mysql> explain SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | ranges | NULL | ALL | hilong,lolong | NULL | NULL | NULL | 7232 | 24.83 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
and even when i try to force it it only uses one index
mysql> explain SELECT * FROM ranges force index (hilong,lolong) WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | ranges | NULL | range | hilong,lolong | hilong | 9 | NULL | 2757 | 65.14 | Using index condition; Using where |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
This doesn't seem right, how can I get MySQL to use both indexes, it seems like it's needlessly scanning rows for this simple looking query