This post originated from an RSS feed registered with Ruby Buzz by Matt Bauer. Original Post: MySQL And GeoIP Feed Title: blogmmmultiworks Feed URL: http://blog.mmmultiworks.com/feed/rss.xml Feed Description: Thoughts on Ruby, hosting and more Latest Ruby Buzz Posts Latest Ruby Buzz Posts by Matt Bauer Latest Posts From blogmmmultiworks

It's 2008 and I have a couple Ruby community goals I want to achieve. One of which is to actually get the GemStats site running. I made an attempt last year but a bad design and lack of time really derailed me. Not this year though. I have a new design that is fast as snot, I'm scheduling time in my calendar to work on it and am getting a designer to help with the UI. I've also thought of some damn cool features to add but first I need to get the basics done.

There are something like 17.4 million records detailing each and every gem download. Each record needs to be sanitized and normalized which requires the geocoding of ip addresses among other things. I'm using MaxMind's Geolocating Products for this. The data is contained in two tables. The first is defined as:

CREATE TABLE geoip_blocks (
  loc_id int(11),
  ip_address_start int(11) unsigned,
  ip_address_end int(11) unsigned
) ENGINE=MyISAM

CREATE TABLE geoip_locations (
    loc_id int(11),
    country_code char(2),
    region_code char(2),
    city varchar(255),
    postal_code varchar(255),
    dma_code varchar(255),
    area_code varchar(255),
    latitude float,
    longitude float
) ENGINE=MyISAM

CREATE INDEX geoip_locations_loc_id ON geoip_locations (loc_id)
CREATE INDEX geoip_locations_country_code ON geoip_locations (country_code)
CREATE INDEX geoip_locations_region_code ON geoip_locations (region_code)
    
CREATE INDEX geoip_blocks_ip_address_start ON geoip_blocks (ip_address_start)
CREATE INDEX geoip_blocks_ip_address_end ON geoip_blocks (ip_address_end)

All ip address are encoded as unsigned integers and MySQL provides two built in functions to convert from dotted octet to the unsigned integer and back: INET_ATON and INET_NTOA. Using these functions we can get the geoip_location field from an ip address with the following query:

SELECT geoip_locations.* FROM geoip_blocks, geoip_locations
WHERE INET_ATON('216.243.185.70') 
BETWEEN geoip_blocks.ip_address_start 
   AND geoip_blocks.ip_address_end 
AND geoip_blocks.loc_id = geoip_locations.loc_id

To make it faster MySQL needs some hints. The first hint to leverage the fact that the geoip_blocks data is already ordered in the table. This means the matching row will be the maximum ip_address_start that is just below the ip address you are searching for. MySQL can be told of this hint by just adding and ORDER BY clause which will scan the ordered ip_address_start index:

SELECT geoip_locations.* FROM geoip_blocks, geoip_locations
WHERE INET_ATON('216.243.185.70') 
BETWEEN geoip_blocks.ip_address_start 
  AND geoip_blocks.ip_address_end 
AND geoip_blocks.loc_id = geoip_locations.loc_id
ORDER BY geoip_blocks.ip_address_start DESC

SELECT geoip_locations.* FROM geoip_blocks, geoip_locations
WHERE INET_ATON('216.243.185.70')
BETWEEN geoip_blocks.ip_address_start
   AND geoip_blocks.ip_address_end
AND geoip_blocks.loc_id = geoip_locations.loc_id
ORDER BY geoip_blocks.ip_address_start DESC LIMIT 1

SELECT geoip_locations.* FROM geoip_blocks
INNER JOIN (
   SELECT MAX(ip_address_start) AS max_ip
     FROM geoip_blocks
     WHERE ip_address_start <= INET_ATON('216.243.185.70')
   ) s ON s.max_ip = geoip_blocks.ip_address_start
INNER JOIN geoip_locations ON geoip_locations.loc_id = geoip_blocks.loc_id
WHERE geoip_blocks.ip_address_end >= INET_ATON('216.243.185.70')

Variations of the above query happen throughout GemStats and as a result I created a function to find the loc_id given an ip address:

CREATE FUNCTION ip_address_to_loc_id(ip_address INT(11) UNSIGNED)
  RETURNS INTEGER
BEGIN
  DECLARE loc_id_var INTEGER;
  SELECT loc_id INTO loc_id_var
  FROM geoip_blocks
  INNER JOIN (
    SELECT MAX(ip_address_start) AS max_ip
    FROM geoip_blocks
    WHERE ip_address_start <= ip_address
   ) s ON s.max_ip = geoip_blocks.ip_address_start
  WHERE ip_address_end >= ip_address
  RETURN loc_id_var;
END

SELECT geoip_locations.* FROM geoip_locations
WHERE ip_address_to_loc_id(INET_ATON('216.243.185.70')) = geoip_locations.loc_id