I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to execute:

SELECT post_id
FROM pageviews
WHERE user_id = 981
GROUP BY post_id

Here is the execution plan:

mysql> EXPLAIN SELECT post_id FROM visits WHERE user_id = 981 GROUP BY post_id;
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows  | Extra                                        |
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | visits | ref  | user_id       | user_id | 5       | const | 54696 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+

I'm not sure how to look for the cause of the slowness: maybe the table is not well configured, the mysql server not well tuned, other queries locking stuff, ... Or maybe just 4M rows is a good size to start partitioning.

Production database is on Amazon RDS

CREATE TABLE `pageviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `post_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `FK_444839EAA76ED395` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `visits_ibfk_2` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4587432 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  • 2
    A group by implicitly does an order by in MySQL <= 5.6 You can skip this by adding ORDER BY NULL or you rewrite the query as SELECT DISTINCT post_id FROM pageviews WHERE user_id = 981; Can you please have a try if it makes a difference? – tombom Nov 14 '14 at 11:16
up vote 6 down vote accepted

In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index.

This will probably lower the query execution significantly if you have a large enough buffer pool and the query is relatively frequent.

If after doing that, the query is still slow, you will need to do (pre)caching in order to speed up the query execution.

Why have an id at all? Why not have PRIMARY KEY (user_id, post_id)?

Why have user_id and post_id nullable? Shouldn't they be NOT NULL?

@jynus is right about a covering index, but if you change the PK as I suggest, that separate index won't be necessary.

innodb_buffer_pool_size should normally be 70% of available RAM.

I don't see how (pre)caching would be useful. Even if you could do it, it would be bumping out other blocks, thereby slowing down others.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.