MySQL：查询之间的最佳索引

我有一个具有以下结构的表：

CREATE TABLE `geo_ip` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `start_ip` int(10) unsigned NOT NULL,
  `end_ip` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `start_ip` (`start_ip`),
  KEY `end_ip` (`end_ip`),
  KEY `start_end` (`start_ip`,`end_ip`),
  KEY `end_start` (`end_ip`,`start_ip`)) ENGINE=InnoDB;

MySQL 似乎无法对我的大多数查询使用索引，因为where子句使用一个between介于两者之间start_ip and end_ip:

select * from geo_ip where 2393196360 between start_ip and end_ip;

+----+-------------+--------+------+-------------------------------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys                       | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+-------------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | geo_ip | ALL  | start_ip,end_ip,start_end,end_start | NULL | NULL    | NULL | 2291578 | Using where |
+----+-------------+--------+------+-------------------------------------+------+---------+------+---------+-------------+

该表有几百万条记录。我尝试通过删除来扩展表格start_ip and end_ip列，并为每个可能的值创建一行start_ip and end_ip as the id，然后查询id。虽然查询性能大大提高，但它导致表大小从不到 1 GB 增长到数十 GB（该表显然还有其他列）。

还可以采取哪些措施来提高查询性能？我可以以某种方式更改查询，或者我可以以不同的方式对列进行索引以导致命中吗？或者也许是我还没有想到的事情？

Edit:

奇怪的是，索引用于某些值。例如：

explain select * from geo_ip where 3673747503 between start_ip and end_ip;
+----+-------------+--------+-------+-------------------------------------+--------+---------+------+-------+-------------+
| id | select_type | table  | type  | possible_keys                       | key    | key_len | ref  | rows  | Extra       |
+----+-------------+--------+-------+-------------------------------------+--------+---------+------+-------+-------------+
|  1 | SIMPLE      | geo_ip | range | start_ip,end_ip,start_end,end_start | end_ip | 4       | NULL | 19134 | Using where |
+----+-------------+--------+-------+-------------------------------------+--------+---------+------+-------+-------------+

不知道为什么，但向查询添加 order by 子句和 limit 似乎总是会导致索引命中，并且在几毫秒而不是几秒内执行。

explain select * from geo_ip where 2393196360 between start_ip and end_ip order by start_ip desc limit 1;
+----+-------------+--------+-------+-----------------+----------+---------+------+--------+-------------+
| id | select_type | table  | type  | possible_keys   | key      | key_len | ref  | rows   | Extra       |
+----+-------------+--------+-------+-----------------+----------+---------+------+--------+-------------+
|  1 | SIMPLE      | geo_ip | range | start_ip,end_ip | start_ip | 4       | NULL | 975222 | Using where |
+----+-------------+--------+-------+-----------------+----------+---------+------+--------+-------------+

现在这对我来说已经足够好了，尽管我很想知道优化器决定在其他情况下不使用索引背后的原因。