How to build an efficient GeoIP SQL table

This here is a very handy little script I threw together to generate a geoip.sql table for quickly determining which country a IP is from. I already hear you saying “Just convert the IP to an INT and use BETWEEN, how hard can it be”. And you are right, that works. And it may even be your easiest solution, but it just isn’t fast. And if you are planning on hammering the table with thousands of queries you are going to end up looking for something fast.

A while back I found a very interesting posting at www.jcole.us┬áthat described how to use Spacial Indexes┬átogether with MySQL’s GIS to speed up the queries. The posting has been online for a while and both it and the replies are worth reading.

All I did was make a small bash script to download the current “lite” version of GeoIP CSV file from maxmind.com, use the information from the posting to throw/transform it into a local database table and dump out a .sql file that can be easily imported into any other database. The script isn’t failproof though, it expects your user to be able to use mysql and have permission to create databases/tables and “load data local infile”.

generate_geoip_sql.sh