Mysql – Convert the IP Address range to two BIGINT for faster lookup

javaMySQL

I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets which will look like below and now the next steps is to populate this datasets in to the MySql table. Below is the dataset example containing columns-

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

Problem Statement:-

I am planning to store the START_IP_NUM and END_IP_NUM as BIGINT in the MySql database instead of storing IP Address in the table as doing a lookup against a BIGINT is far faster than searching a string (dotted ip notation).

So my question is we will be having separate Java Program 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-

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

I am confuse given a IP Address how should I device start_ip_num and end_ip_num here and store it into the MySql table

Best Answer

If I am reading your requirements correctly.

From the dataset provided the start_ip_num and end_ip_num will be the same.

Why you may ask? you are not storing a range of ip addresses; take for example a /24 range: 192.168.1.0/24 this provides a range 192.168.1.0 - 192.168.1.255, whilst really only ip's 192.168.1.1 though 192.168.1.254 are usable (.255 being the broadcast).

If you are storing a 'range' the start and end values will differ

mysql> select INET_ATON('192.168.1.1') as start_ip_num, INET_ATON('192.168.1.254') as end_ip_num;
+--------------+------------+
| start_ip_num | end_ip_num |
+--------------+------------+
|   3232235777 | 3232236030 |
+--------------+------------+
1 row in set (0.00 sec)

If not they will be the same, if you want the ranges for each a whois 24.32.116.116 will reveal this information.

e.g.

24.32.116.116 has 24.32.0.0/16

and 24.32.116.0/22 "Comment: Clarksville, Tx Customers"

The later providing the required information.

As such your 'range' is strictly speaking: 24.32.116.0 - 24.32.119.255

mysql> select INET_ATON('24.32.116.0') as start_ip_num, INET_ATON('24.32.116.255') as end_ip_num;
+--------------+------------+
| start_ip_num | end_ip_num |
+--------------+------------+
|    404780032 |  404780287 |
+--------------+------------+
1 row in set (0.00 sec)

REASON #1 : No Redundancy

Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.

REASON #2 : Mild Disk I/O

No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.

Please read past posts on the pros and cons of the MEMORY Storage Engine

May 22, 2011 : I am using the MEMORY storage engine but MySQL still writes to my disk...Why?
Sep 26, 2011 : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
Jan 17, 2012 : Mysql Memory table getting many locks
Jan 20, 2012 : How much memory will a MEMORY table take up?

RECOMMENDATION

Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?

Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:

STEP 01) Create the table like this:

CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:

cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist; >> init-file.sql

STEP 03) Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL

service mysql restart

That's it.

Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).

Why not use InnoDB?

The data and index pages would have the data twice in RAM.
Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.

Mysql – Relational database for address model

Here's what we did in LedgerSMB:

Entity table storing basic container info on individuals and businesses
location table storing address information.
Right now we have a join table between address and entity, but in future versions that will probably be merged with the address table.
Tables 'person' and 'company' which extend entity for that purpose.

That basic structure would work on MySQL too. On PostgreSQL we do some magic with partial unique indexes to make sure that billing addresses are unique per entity, but contact addresses are not. This would not be possible to enforce in MySQL as easily though it could be done with creative design.

Best Answer

Related Solutions

Mysql – Is the Memory engine adequate for a read intensive/lookup table

REASON #1 : No Redundancy

REASON #2 : Mild Disk I/O

RECOMMENDATION

Mysql – Relational database for address model

Related Question