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.