mysql - 是否可以使这个推荐系统 SQL 查询更快？

Question

我有 2 个表，其中一个存储项目，另一个存储喜欢。

存储喜欢的表称为 video_liked，有 2 列，video_id 和 user_id，有 2 个索引 - video_id-user_id (UNIQUE) 和 user_id-video_id (PRIMARY)。

另一个表称为视频，具有主索引和自动递增列 ID。

我正在尝试获取与喜欢观众正在观看的相同用户喜欢的项目列表，按喜欢它们的人数排序，最少有 2 个喜欢。

我正在使用的查询是

SELECT vid . * , count( video_liked1.user_id ) AS PersonCount
FROM video AS vid, video_liked, video_liked AS video_liked1
WHERE video_liked.user_id = video_liked1.user_id
AND video_liked.video_id <> video_liked1.video_id
AND video_liked1.video_id = 'ITEM_ID'
AND vid.id = video_liked.video_id
GROUP BY video_liked.video_id
HAVING count( video_liked1.user_id ) >2
ORDER BY PersonCount DESC
LIMIT 12

like多的时候查询很慢，所以我把它简化成最基本的结构

SELECT vid. *
FROM video AS vid, video_liked, video_liked AS video_liked1
WHERE video_liked.user_id = video_liked1.user_id
AND video_liked.video_id <> video_liked1.video_id
AND video_liked1.video_id = 'ITEM_ID'
AND vid.id = video_liked.video_id
GROUP BY video_liked.video_id
LIMIT 12

它稍微快一点，但仍然需要 0.05 秒才能在具有 28k 行的 likes 表上执行

EXPLAIN 给我的输出太宽而无法在没有自动换行的情况下放在这里，所以这里是一个指向 pastebin 的链接

http://pastebin.com/raw.php?i=6edwdniQ

这是我在 pastebin 中的表格

http://pastebin.com/raw.php?i=jwK1QucA

编辑:

按照建议更改查询

SELECT vid . *, count( v1.user_id ) AS PersonCount
FROM video AS vid
JOIN video_liked AS v1 ON vid.id = v1.video_id
JOIN video_liked AS v2 ON v2.video_id = 'ITEM_ID'
AND v1.user_id = v2.user_id
AND v1.video_id <> v2.video_id
GROUP BY v1.video_id
ORDER BY PersonCount DESC
LIMIT 12 

缓慢的罪魁祸首似乎是使用创建临时表的 GROUP BY。

Answer 1

从您的查询中删除CROSS JOIN。这些会使您的数据集膨胀。

SELECT vid. *
FROM video AS vid
JOIN video_liked  AS v1 ON vid.video_id = v1.video_id
JOIN video_liked AS v2 ON v2.video_id = 'ITEM_ID' AND v1.user_id=v2.user_id AND v1.video_id <> v2.video_id
GROUP BY video_liked.video_id
LIMIT 12