title: Convert the IP Address range to two BIGINT for faster lookup
body: <p>I am working on a project in which we need to do <code>IP Address lookups</code>. So for this we have got the datasets which will look like below and now the next steps is to populate this datasets (may be a csv file) in to the <code>MySql table</code>. Below is the dataset example containing columns-</p>
  
  <pre><code>ip_address      country region  city           metro-code   latitude    longitude   postal-code   country-code   region-code    city-code   continent-code  country-code
  
  24.32.116.116   usa       tx    clarksville    623           33.6103    -95.0498    75426            840           44             34918            6            us
  65.29.108.232   usa       mi    novi           505           42.4637    -83.4604    48375            840           23             4339             6            us
  66.169.99.69    usa       tx    ft worth       623           32.7016    -97.3823    76109            840           44             771              6            us
  72.70.100.111   usa       ma    peabody        506           42.5332    -70.9726    1960             840           22             1392             6            us
  174.252.116.137 usa       va    oakton         511           38.8892    -77.3222    22124            840           47             3860             6            us
  68.55.144.242   usa       md    pasadena       512           39.1276    -76.5125    21122            840           21             4358             6            us
  174.252.83.27   usa       pa    lancaster      566           40.0459    -76.3542    17603            840           39             333              6            us
  174.78.192.31   usa       ga    warner robins  503           32.5958    -83.6384    31088            840           11             5052             6            us
  98.192.146.235  usa       fl    panama city    656           30.1804    -85.5598    32404            840           10             671              6            us
  71.192.181.20   usa       ma    springfield    543           42.1187    -72.5483    1109             840           22             967              6            us
  76.183.54.227   usa       tx    dallas         623           32.7202    -96.6769    75217            840           44             77               6            us
  69.62.143.140   usa       ca    granite bay    862           38.7442    -121.191    95746            840           5              49451            6            us
  69.142.76.32    usa       nj    penns grove    504           39.707     -75.4467    8069             840           31             2335             6            us
  70.173.54.93    usa       nv    las vegas      839           36.2059    -115.225    89108            840           29             173              6            us
  98.242.8.222    usa       ca    fresno         866           36.7968    -119.883    93722            840           5              19               6            us
  </code></pre>
  
  <p><strong>Problem Statement:-</strong></p>
  
  <p>I am planning to store the <code>START_IP_NUM</code> and <code>END_IP_NUM</code> as <code>BIGINT</code> in the MySql database instead of storing <code>IP Address</code> in the table as doing a lookup against a BIGINT is far faster than searching a string (dotted ip notation). Just like we have database setup for Maxmind.</p>
  
  <p>So my question is we will be having separate <code>Java Program</code> that will populate the above datasets in the MySql table. So I need to device some sort of logic which can convert the above datasets into like below-</p>
  
  <pre><code>start_ip_num
  end_ip_num
  country 
  region  
  city           
  metro-code  
  latitude    
  longitude   
  postal-code   
  country-code     
  region-code 
  city-code   
  continent-code  
  country-code
  </code></pre>
  
  <p>I am confuse given a IP Address how should I device <code>start_ip_num</code> and <code>end_ip_num</code> here and store it into the <code>MySql table</code>. As there will be a file I am assuming which will contain the datasets like above and then I need to read that file and device some sort of logic to convert the IP Address to two BIGINT and store it into the MySql table.</p>
  
  <p>And after storing it into MySql database. I can do lookup something like this in MySql-</p>
  
  <pre><code>SELECT country
  FROM geoip
  WHERE
  INET_ATON('174.36.207.186') BETWEEN start_ip_num AND end_ip_num
  LIMIT 1
  </code></pre>
  
  <p><strong>UPDATE:-</strong></p>
  
  <p>Suppose I have around couple of <code>Thousands IP Address</code> like <code>100,000 IP Addresses</code> in a text file with the format I mentioned above. </p>
  
  <p>Now my main Objective is to do the <code>IP Address lookup</code>. Corresponding to this <code>IP Address</code> get me all the other required fields.</p>
  
  <p>So to make this thing work, I was initially planning to dump the text file data as it is in the <code>MySql table</code>. So MySql table will contain <code>IP Address</code> column and other columns just like I have in the above example. But doing a lookup on the String is expensive.</p>
  
  <p>So I thought I should convert those IP Address in <code>start_ip_num</code> and <code>end_ip_num</code> range and then dump the data in <code>MySql table</code> so now it will look something like this-</p>
  
  <pre><code>start_ip_num
  end_ip_num
  country 
  region  
  city           
  metro-code  
  latitude    
  longitude   
  postal-code   
  country-code     
  region-code 
  city-code   
  continent-code  
  country-code
  </code></pre>
  
  <p>And now if I need to do the lookups, I can have this <code>SQL query</code> embedded in my <code>WebService eventually</code>-</p>
  
  <pre><code>SELECT country
  FROM geoip
  WHERE
  INET_ATON('174.36.207.186') BETWEEN start_ip_num AND end_ip_num
  LIMIT 1
  </code></pre>
  
  <p><strong>So question is-</strong> Given a <code>Text file</code> which will have list of <code>IP Addresses</code> and other fields corresponding to that <code>IP Address</code> how should I massage it in such a way such that I can store the same datasets in MySql in the format I mentioned above such as <code>start_ip_num</code> and <code>end_ip_num</code>, <code>country</code> and other fields.</p>
  
  <p>Basically I am trying to follow the pattern of database such as Maxmind have currently. They also have <code>start_ip_num</code> and <code>end_ip_num</code> and other fields. And when you need to do the lookups you can do the lookups basis on the SQL I mentioned above by converting the IP Address to <code>BIGINT</code> using <code>INET_ATON</code>.</p>