title: MySQL: Optimal index for between queries
body: <p>I have a table with the following structure:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>MySQL seems to be unable to use the indexes for most of my queries, as the <code>where</code> clause uses a <code>between</code> that falls somewhere between <code>start_ip</code> and <code>end_ip</code>:</p>
  
  <pre><code>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 |
  +----+-------------+--------+------+-------------------------------------+------+---------+------+---------+-------------+
  </code></pre>
  
  <p>The table has a few million records. I tried expanding the table, by removing the <code>start_ip</code> and <code>end_ip</code> columns, and creating a row for every possible value of <code>start_ip</code> and <code>end_ip</code> as the <code>id</code>, then querying the <code>id</code>. While that vastly improved query performance, it resulted in the table size growing from less than a gigabyte to tens of gigabytes (the table has other columns obviously).</p>
  
  <p>What else can be done to improve query performance? Can I change the query somehow, or can I index the columns differently to result in a hit? Or perhaps something I haven't thought of yet?</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>Strangely, the index is used for certain values. For example:</p>
  
  <pre><code>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 |
  +----+-------------+--------+-------+-------------------------------------+--------+---------+------+-------+-------------+
  </code></pre>