I'm running a query that touches 2 moderately sized tables (1-5 million rows). EXPLAIN shows it touches only indexes and very few rows, however it takes very long to complete (~8 seconds). The only thing that might be of concern is the temporary table it creates. If anyone has any ideas, it would be appreciated!
Query:
SELECT `visits`.`id`
FROM `visits`
INNER JOIN `visits_views` ON visits_views.visit_id=visits.id
GROUP BY `visits`.`id`
ORDER BY `visits`.`created` DESC
LIMIT 20
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE visits index PRIMARY created 4 NULL 20 Using index; Using temporary
1 SIMPLE visits_views ref visit_id visit_id 4 visits.id 1 Using index
Table structure
CREATE TABLE `visits` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`affiliate_id` int(11) DEFAULT NULL,
`referer` varchar(250) DEFAULT NULL,
`domain` varchar(70) DEFAULT NULL,
`user_agent` varchar(300) DEFAULT NULL,
`country` char(2) DEFAULT NULL,
`ip` int(10) unsigned DEFAULT NULL,
`bot` tinyint(1) NOT NULL DEFAULT '0',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `domain` (`domain`),
KEY `created` (`created`),
KEY `referer` (`referer`),
KEY `ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `visits_views` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`visit_id` int(11) NOT NULL,
`url` varchar(250) NOT NULL,
`prev_id` int(11) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `visit_id` (`visit_id`),
KEY `created` (`created`),
KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Aucun commentaire:
Enregistrer un commentaire