Re: Slow JOIN to convert IP to Country Code

[Harutyun Drnoyan]

Hello.

Here are necessary queries.

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

CREATE TABLE `iplist` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` char(2) NOT NULL,
  `code_3` char(3) NOT NULL,
  `name` char(255) NOT NULL,
  `start` int(11) unsigned NOT NULL,
  `end` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
  KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=91748 DEFAULT CHARSET=latin1;

EXPLAIN SELECT iplist.code, iplist.name, COUNT(stat.ip_interval ) AS COUNT
FROM stats AS stat
LEFT JOIN iplist AS iplist ON (iplist.id=stat.ip_interval)
WHERE stat.dateline BETWEEN 1243581116 AND 1243667516
GROUP BY CODE
ORDER BY COUNT DESC

id		select_type		table		type		possible_keys		key		key_len		ref		rows		Extra
1		SIMPLE		stat		range		dateline,ip_interval,location_id		ip_interval		4		\N		495659		Using where; Using index; Using temporary; Using filesort
1		SIMPLE		iplist		eq_ref		PRIMARY		PRIMARY		4		vb38.stat.ip_interval		1

Adding summery table does not seem to me good idea as this data will be highly dynamic and I want to provide ability to get statistics for any time period and not for defined periods (day, week, month). I understand that this query can not take too less time as it does a lot of work to group and count results. I'm just looking to way to make it as fast as possible.

Thanks for your help.

P.S. Could you please suggest me any flexible way to paste explain results in posts. The tab-delimited text always gets corrupted in post text. Thanks.



Edited 1 time(s). Last edit at 05/30/2009 02:54AM by Harutyun Drnoyan.