C3rd

MySQL SPATIAL Index for GeoIP Database

Posted: 6 Feb 2013, 2:32am - Wednesday

Few days ago, I had an experimental project called Website Visitor Tracker which will track the visitor what city or country they from and plot their location to Google WebGL Globe and the source of the data have to query in 2M+ rows for the IP blocks and 290k+ rows for the cities or 91k+ rows for the countries. The problem occurs is the the query which it will take 2 to 4 seconds each IP query from the database and it is bad sign. I found a solution suggested by BOTP from Kagaya-anon Linux Users Group (Thanks master BOTP) that I have to use SPATIAL Index. So I did and it works like a charm. This is the output without using SPATIAL Index...

mysql> SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b, geoip_locations AS l WHERE b.locID = l.locID AND INET_ATON('203.114.138.95') BETWEEN b.startIpNum AND b.endIpNum;
+---------+------------------+----------+------------+
| id      | city             | latitude | longtitude |
+---------+------------------+----------+------------+
| 1859525 | Palmerston North | -40.3500 |   175.6167 |
+---------+------------------+----------+------------+
1 row in set (1.71 sec)

mysql>

So here's the steps implementing SPATIAL index... First I created a new table...

create table geoip_blocks (
	id bigint(20) unsigned not null auto_increment primary key,
	locID bigint(20) not null default 0,
	startIpNum bigint(20) unsigned not null,
	endIpNum bigint(20) unsigned not null,
	ip_poly POLYGON not null,
	SPATIAL INDEX(ip_poly)
) engine=myisam;

Note: use MyISAM engine for this to apply SPATIAL index, otherwise you will encounter some issues. :P Next, export your current table of your GeoIP...

WINDOWS: 

select locID, startIpNum, endIpNum into outfile 
'c:/geoip_blocks.dat' fields terminated by "," enclosed by "\"" 
lines terminated by "\n" from geoip_ip_blocks;

LINUX:

select locID, startIpNum, endIpNum into outfile 
'/home/user/geoip_blocks.dat' fields terminated by "," 
enclosed by "\"" lines terminated by "\n" from geoip_ip_blocks;

Then import again but this time into the new table where you will insert into POLYGON type field. WINDOWS:

LOAD DATA LOCAL INFILE "c:/geoip_blocks.dat"
         INTO TABLE geoip_blocks 
         FIELDS
            TERMINATED BY ","
             ENCLOSED BY "\""
         LINES
              TERMINATED BY "\n"
           (
               @locID, @startIpNum, @endIpNum 
             )
       SET
            id := NULL,
            locID := @locID,
           startIpNum := @startIpNum,
           endIpNum := @endIpNum,
           ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
          POINT(@startIpNum, -1),
          POINT(@endIpNum, -1),
          POINT(@endIpNum, 1),
          POINT(@startIpNum, 1),
         POINT(@startIpNum, -1))));

LINUX:

LOAD DATA LOCAL INFILE "/home/user/geoip_blocks.dat"
         INTO TABLE geoip_blocks 
         FIELDS
            TERMINATED BY ","
             ENCLOSED BY "\""
         LINES
              TERMINATED BY "\n"
           (
               @locID, @startIpNum, @endIpNum 
             )
       SET
            id := NULL,
            locID := @locID,
           startIpNum := @startIpNum,
           endIpNum := @endIpNum,
           ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
          POINT(@startIpNum, -1),
          POINT(@endIpNum, -1),
          POINT(@endIpNum, 1),
          POINT(@startIpNum, 1),
         POINT(@startIpNum, -1))));

And that's it.. You are good to go for testing...

OUTPUT AFTER SPATIAL INDEX IMPLEMENTED:

mysql> SELECT geoip_blocks.locID, geoip_blocks.startIpNum, geoip_blocks.endIpNum
    ->  FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID
    ->          WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.95'), 0)));
+--------+------------+------------+
| locID  | startIpNum | endIpNum   |
+--------+------------+------------+
| 199902 | 3413280768 | 3413283071 |
+--------+------------+------------+
1 row in set (0.19 sec)

mysql> SELECT geoip_blocks.locID, geoip_locations.city, geoip_blocks.startIpNum, geoip_blocks.endIpNum
    ->  FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID
    ->          WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.95'), 0)));
+--------+------------------+------------+------------+
| locID  | city             | startIpNum | endIpNum   |
+--------+------------------+------------+------------+
| 199902 | Palmerston North | 3413280768 | 3413283071 |
+--------+------------------+------------+------------+
1 row in set (0.00 sec)

Hope this help for those who encounter the same problem I had... :) Thanks again to Master BOTP & DATA BOB JR. Reference: http://databobjr.blogspot.co.nz/2010/07/ip-to-country-lookup-table-using-mysql.html

Back to list

MySQL SPATIAL Index for GeoIP Database

Search

Pages

Categories