Effective ip->location query

Question

I have two tables: one is ip_group_city from ipinfodb.com containing ip_start numbers for determining location of IPs, and other is "visits" with information about web site visitor containing column 'ip'.

I need to select top 10 region_code (from ip_group_city) by checking region_code for each IP from "visits" table.

Right now I'm loading all IPs from "visits" into an array and using that IP info to query the ip_group_city by:

SELECT region_code
FROM ip_group_city
WHERE ip_start <= INET_ATON(IP_FROM_ARR)
ORDER BY ip_start DESC LIMIT 1

I'm unable to create some sort of nested query to do the job for me, because right now things are a bit slow :) - it takes up to 30s on my laptop xampp (AMD Turion x2 2GHz, running windows 7 ultimate 64bit version)

Here's the table with IP addresses (visits)

CREATE TABLE IF NOT EXISTS `visits` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`clientid` mediumint(8) unsigned NOT NULL,
`ip` varchar(15) NOT NULL,
`url` varchar(512) NOT NULL,
`client_version` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=49272 ;

Thanks

Answer 1

Since you said other solutions are welcomed...

You might want to check out MaxMind. They have good country and city lookups by IP. You can install an Apache or PHP plugin to make it fast - don't even have to handle the database yourself.