Effective ip->location query

Question

Hello all,

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

Hi, Do you index "ip_start" field for improve performance?

Answer 2

To index your table:

ALTER TABLE `ip_group_city` ADD INDEX ( `ip_start` )

To get the top 10 region_codes:

SELECT igc.region_code
FROM ip_group_city igc
JOIN visits v ON igc.ip_start = v.ip
GROUP BY igc.region_code
ORDER BY COUNT(*) DESC
LIMIT 10

Answer 3

ALTER TABLE `ip_group_city` ADD INDEX ( `ip_start` )

thats all i'm saying. be sure to use btree and not hash :D