Slow query on mid-size tables with good index coverage

Question

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;

Answer 1

In order to make very small temp tables, you need to rewrite the query to do this:

• LIMIT first on the 20 most recent created timestamps
• then JOIN those 20 ids to visits_views

Here is the proposed query

SELECT A.id FROM
(SELECT id FROM visits ORDER BY created DESC LIMIT 20) A
LEFT JOIN `visits_views` B ON A.id = B.visit_id;

I use LEFT JOIN to preserve the order of the subquery

If you want to count the views

SELECT * FROM
(
 SELECT A.id,A.created,COUNT(1) views FROM
 (SELECT id,created FROM visits ORDER BY created DESC LIMIT 20) A
 LEFT JOIN `visits_views` B ON A.id = B.visit_id
 GROUP BY A.id,A.created
) AA ORDER BY created;

I have addressed making small temp tables in queries like this. See my post Fetching a Single Row from Join Table in StackOverflow. It demonstrates how I did a LIMIT 40 on a subquery and used that 40 row result set to JOIN to other tables.

• Eran Galperin: It works very well, query now runs in the sub-millisecond range. I'm wondering you added the additional external SELECT clause for ordering in your last query - ordering the inner SELECT by creation date seems to work fine.