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.