MySQL Forums :: Performance :: Slow JOIN to convert IP to Country Code


Advanced Search

Slow JOIN to convert IP to Country Code
Posted by: Harutyun Drnoyan ()
Date: May 19, 2009 05:05AM

Hello.

I've a table which holds all data about banner (banner id, date of click, IP, browser etc), and table for all IP ranges mapped to countries. Now trying to get statistics of countries clicked the banner in specified time period.

I think it must be done by selecting all events in specified time range along with country code of IP. So I decided to use LEFT JOIN for this.

Here is information about my tables
SHOW CREATE TABLE stats;

CREATE TABLE `stats` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `banner_id` int(11) unsigned NOT NULL,
 `location_id` smallint(3) NOT NULL,
 `url_id` int(11) unsigned NOT NULL,
 `page_id` int(11) unsigned NOT NULL,
 `dateline` int(11) unsigned NOT NULL,
 `ip` int(11) unsigned NOT NULL,
 `browser_id` smallint(3) unsigned NOT NULL,
 `platform_id` smallint(3) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `banner_id` (`banner_id`),
 KEY `dateline` (`dateline`)
) ENGINE=MyISAM AUTO_INCREMENT=44566879 DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=FIXED


SHOW CREATE TABLE iplist;

CREATE TABLE `iplist` (
 `code` varchar(2) NOT NULL,
 `code_3` varchar(3) NOT NULL,
 `name` varchar(255) NOT NULL,
 `start` int(11) unsigned NOT NULL,
 `end` int(11) unsigned NOT NULL,
 UNIQUE KEY `start` (`start`,`end`,`code`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Query to get data for one day is
SELECT stat.ip, iplist.code FROM stats AS stat
LEFT JOIN iplist AS iplist ON (stat.ip BETWEEN iplist.start AND iplist.end)
WHERE dateline>=1242604800 AND dateline<1242691200;
716 rows in set (1 min 32.76 sec)

EXPLAIN SELECT stat.ip, iplist.code FROM stats AS stat
LEFT JOIN iplist AS iplist ON (stat.ip BETWEEN iplist.start AND iplist.end)
WHERE dateline>=1242604800 AND dateline<1242691200 

id	select_type	table	type	possible_keys	key		key_len	ref	rows	Extra
1	SIMPLE		stat	range	dateline	dateline	4	NULL	20129	Using where
1	SIMPLE		iplist	index	start		start		269	NULL	91747	Using index

Table stats has ~45 000 000 rows, iplist has ~96000 rows.

So that's the question - is 1 min 32 sec. normal time to do this task or there is any way to optimize this query?

P.S. The same query without JOIN part takes only 0.0013 sec to run!

Thanks.

Options: ReplyQuote


Subject Views Written By Posted
Slow JOIN to convert IP to Country Code 4160 Harutyun Drnoyan 05/19/2009 05:05AM
Re: Slow JOIN to convert IP to Country Code 1959 Aftab Khan 05/19/2009 07:11AM
Re: Slow JOIN to convert IP to Country Code 1820 Rick James 05/19/2009 11:16PM
Re: Slow JOIN to convert IP to Country Code 1881 Harutyun Drnoyan 05/21/2009 01:34AM
Re: Slow JOIN to convert IP to Country Code 1758 Harutyun Drnoyan 05/27/2009 11:58AM
Re: Slow JOIN to convert IP to Country Code 1823 Rick James 05/27/2009 08:05PM
Re: Slow JOIN to convert IP to Country Code 1772 Harutyun Drnoyan 05/27/2009 10:13PM
Re: Slow JOIN to convert IP to Country Code 1904 Harutyun Drnoyan 05/27/2009 07:04AM
Re: Slow JOIN to convert IP to Country Code 1736 Rick James 05/27/2009 09:46AM
Re: Slow JOIN to convert IP to Country Code 1713 Harutyun Drnoyan 05/27/2009 10:05PM
Re: Slow JOIN to convert IP to Country Code 1782 Harutyun Drnoyan 05/29/2009 10:55AM
Re: Slow JOIN to convert IP to Country Code 1820 Rick James 05/29/2009 03:34PM
Re: Slow JOIN to convert IP to Country Code 1934 Harutyun Drnoyan 05/30/2009 01:43AM
Re: Slow JOIN to convert IP to Country Code 1875 Rick James 05/30/2009 10:47AM
Re: Slow JOIN to convert IP to Country Code 1787 Harutyun Drnoyan 05/31/2009 11:10PM
Re: Slow JOIN to convert IP to Country Code 2077 Harutyun Drnoyan 06/01/2009 04:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.