mysql - 优化 GROUP BY&ORDER BY 查询

Question

我有一个网页供用户上传和观看视频。上周我asked跟踪视频观看次数的最佳方式是什么，以便我可以显示本周观看次数最多的视频(所有日期的视频)。

现在我需要一些帮助来优化我从数据库中获取视频的查询。相关的表格是这样的:

video (~239371 rows)
VID(int), UID(int), title(varchar), status(enum), type(varchar), is_duplicate(enum), is_adult(enum), channel_id(tinyint)

signup (~115440 rows)
UID(int), username(varchar)

videos_views (~359202 rows after 6 days of collecting data, so this table will grow rapidly)
videos_id(int), views_date(date), num_of_views(int)

表 video 包含视频，signup hodls 用户和 videos_views 包含有关视频观看的数据(每个视频每天可以有一行在那个表中)。

我有一个可以解决问题的查询，但需要大约 10 秒的时间来执行，我想随着时间的推移，随着 videos_views 表的大小增加，情况只会变得更糟。

SELECT
 v.VID, 
 v.title, 
 v.vkey, 
 v.duration, 
 v.addtime, 
 v.UID, 
 v.viewnumber, 
 v.com_num, 
 v.rate, 
 v.THB, 
 s.username,
 SUM(vvt.num_of_views) AS tmp_num
FROM
 video v
  LEFT JOIN videos_views vvt ON v.VID = vvt.videos_id
  LEFT JOIN signup s on v.UID = s.UID
WHERE
 v.status = 'Converted'
 AND v.type = 'public'
 AND v.is_duplicate = '0'
 AND v.is_adult = '0'
 AND v.channel_id <> 10
 AND vvt.views_date >= '2001-05-11'
GROUP BY
 vvt.videos_id
ORDER BY
 tmp_num DESC
LIMIT
 8

所有相关字段都已编入索引。这是 EXPLAIN 结果的屏幕截图:

那么，我该如何优化呢？

更新这是我根据 Quassnoi 的回答提出的问题。它返回正确的视频，但它弄乱了注册表中的 JOIN。对于某些记录，username 字段为 NULL，对于其他记录，它包含错误的用户名。

SELECT
    v.VID,
    v.title,
    v.vkey,
    v.duration,
    v.addtime,
    v.UID,
    v.viewnumber,
    v.com_num,
    v.rate,
    v.THB,
    s.username
FROM
    (SELECT
        videos_id,
        SUM(num_of_views) AS tmp_num
    FROM
        videos_views
    WHERE
        views_date >= '2010-05-13'
    GROUP BY
        videos_id
    ) q
        JOIN video v ON v.VID = q.videos_id
        LEFT JOIN signup s ON s.UID = v.VID
WHERE
    v.type = 'public'
    AND v.channel_id <> 10
    AND v.is_adult = '0'
    AND is_duplicate = '0'
ORDER BY
    tmp_num DESC
LIMIT
    8

这是结果集:

Answer 1

是的，计算列上的 ORDER BY 总是不可索引的。对不起。

如果您要经常执行此查询，并且希望避免每次都对每个视频的观看次数进行计数和排序，则必须进行非规范化。添加一个 views_in_last_week 列，每天在后台从 videos_views 重新计算它，并对其进行索引(可能在具有其他相关 WHERE 条件的复合索引中)。