title: How to further optimize this MySQL table for a single Query
body: <p>I have a InnoDB MySql Geo ID table that has ~ 1 million rows. The table structure is this:</p>
  
  <pre><code>CREATE TABLE `geoid` (
     `start_ip` int(11) NOT NULL,
     `end_ip` int(11) NOT NULL,
     `city` varchar(64) NOT NULL,
     `region` char(2) NOT NULL,
     PRIMARY KEY (`start_ip`,`end_ip`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>There will only be one type query ran against this table:</p>
  
  <pre><code>SELECT city, region FROM geoid WHERE 1259650516 BETWEEN start_ip AND end_ip
  </code></pre>
  
  <p>This query takes about ~ .4228 sec, which is not super slow but not incredibly fast ether.</p>
  
  <p>My question is: How can I further optimize my table for this single query?</p>
  
  <p>I have tried the following things: </p>
  
  <ol>
  <li>Change the Storage Engine to MyISAM, this made the query take about 1.9 sec.</li>
  <li>Use the WHERE statement 'WHERE geoid.start_ip &lt;= 1259650516 AND 1259650516 &lt;= geoid.end_ip'. But that takes about .5 sec to execute instead of .4 ish.</li>
  </ol>
  
  <p>I have removed all useless rows from the table to make it smaller. I need all 1 million rows.</p>
  
  <p>UPDATE / SOLUTION</p>
  
  <p>Thanks to the article below, here is what I did to fix this problem. (just to complete this answer for anyone else interested)</p>
  
  <p>I added a new column to the above table:</p>
  
  <pre><code>ALTER TABLE `geoid` ADD `geoip` LINESTRING NOT NULL
  </code></pre>
  
  <p>I then filled the new column with the geo data from start_ip and end_ip</p>
  
  <pre><code>GeomFromText(CONCAT('LINESTRING(', start_ip, ' -1, ', end_ip, ' 1)'))
  </code></pre>
  
  <p>I then created the SPATIAL INDEX on the new column</p>
  
  <pre><code>CREATE SPATIAL INDEX geoip_index ON geoid(geoip);
  </code></pre>
  
  <p>From there, all you have to do is change your query to:</p>
  
  <pre><code>SELECT city, region FROM geoid WHERE MBRContains(geoip, GeomFromText(CONCAT('POINT(', 1259650516, ' 0)')));
  </code></pre>
  
  <p>AND YOUR DONE. This took the query down from .42 sec to .0003 sec!!!!!!!</p>
  
  <p>I love this INDEX. Thank you. Hope it helps.</p>