sql - 范围索引查询后优化 GROUP BY

Question

我有一个内容应用程序，它需要计算时间片中的响应，然后按响应数量对它们进行排序。它目前适用于小型数据集，但需要扩展到数百万行。我当前的查询不起作用。

mysql> describe Responses;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| site_id       | int(10) unsigned    | NO   | MUL | NULL    |       |
| content_id    | bigint(20) unsigned | NO   | PRI | NULL    |       |
| response_id   | bigint(20) unsigned | NO   | PRI | NULL    |       |
| date          | int(10) unsigned    | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+

表类型为InnoDB，主键为on(content_id, response_id)。在 (content_id, date) 上有一个附加索引用于查找对一段内容的响应，在查询中使用的另一个附加索引 (site_id, date) 我遇到了麻烦:

mysql> explain SELECT content_id id, COUNT(response_id) num_responses
               FROM Responses
               WHERE site_id = 1
                 AND date > 1234567890
                 AND date < 1293579867
               GROUP BY content_id
               ORDER BY num_responses DESC
               LIMIT 0, 10;
+----+-------------+-----------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra                                                     |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | Responses | range | date          | date | 8       | NULL |  102 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-----------------------------------------------------------+

这是我能想到的最好的结果，但它最终会在需要计算的 1,000,000 行中，导致 10,000 行需要排序，以拉入少数结果。

我也想不出一种方法来预先计算计数，因为日期范围是任意的。我可以自由更改主键:它可以由任意顺序的 content_id、response_id 和 site_id 组成，但不能包含日期。

该应用程序主要是用 PHP 开发的，因此如果有更快的方法通过将查询拆分为子查询、使用临时表或在应用程序端执行操作来完成相同的结果，我愿意接受建议。

Answer 1

(应要求从评论中转贴)

设置一个包含三列的表:id、date 和 num_responses。列 num_responses 包含给定日期给定 id 的响应数。适本地回填表，然后在每晚午夜左右(或更晚)运行一个脚本，为前一天添加一个新行。

然后，要得到你想要的行，你可以只查询上面提到的表。