我正在用Python和MySQL构建一个视频推荐网站(类似于Pandora音乐的推荐),我的数据库中有三个表:
video - 存储视频信息,数据不会改变。列包括:
CREATE TABLE `video` (
id int(11) NOT NULL AUTO_INCREMENT,
website_id smallint(3) unsigned DEFAULT '0',
rating_global varchar(128) DEFAULT '0',
title varchar(256) DEFAULT NULL,
thumb_url text,
PRIMARY KEY (`id`),
KEY `websites` (`website_id`),
KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=49362 DEFAULT CHARSET=utf8
video_tag - 与每个视频相关联的标签(属性)的表格。不会改变。
CREATE TABLE `video_tag` (
id int(7) NOT NULL AUTO_INCREMENT,
video_id mediumint(7) unsigned DEFAULT '0',
tag_id mediumint(7) unsigned DEFAULT '0',
PRIMARY KEY (`id`),
KEY `video_id` (`video_id`),
KEY `tag_id` (`tag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=562456 DEFAULT CHARSET=utf8
用户评分 - 用户对每个标签的好坏评级表格。数据始终在变化。
CREATE TABLE `user_rating` (
id int(11) NOT NULL AUTO_INCREMENT,
user_id smallint(3) unsigned DEFAULT '0',
tag_id int(5) unsigned DEFAULT '0',
tag_rating float(10,5) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `video` (`tag_id`),
KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=447 DEFAULT CHARSET=utf8
根据用户的偏好,我希望对每个未观看的视频进行评分,并尝试预测他们最喜欢的内容。这导致了以下庞大的查询,对于50,000个视频需要约2秒钟才能完成:
SELECT video_tag.video_id,
(sum(user_rating.tag_rating) * video.rating_global) as score
FROM video_tag
JOIN user_rating ON user_rating.tag_id = video_tag.tag_id
JOIN video ON video.id = video_tag.video_id
WHERE user_rating.user_id = 1 AND video.website_id = 2
AND rating_global > 0 AND video_id NOT IN (1,2,3) GROUP BY video_id
ORDER BY score DESC LIMIT 20
我急需提高效率,所以我正在寻求建议,希望了解最佳方向。有一些想法:
a) 重构我的数据库表结构(不确定如何)
b) 将更多的分组和聚合工作转移至Python中(还没有找到一个真正快速的连接三个表的方式)
c) 尝试将不变的表存储在内存中以尝试加快计算时间(早期的尝试还没有带来任何收益...)
您会推荐如何使其更有效率呢?
非常感谢!
--
根据评论的请求,EXPLAIN SELECT...显示:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user_rating ref video,user_id user_id 3 const 88 Using where; Using temporary; Using filesort
1 SIMPLE video_tag ref video_id,tag_id tag_id 4 db.user_rating.tag_id 92 Using where
1 SIMPLE video eq_ref PRIMARY,websites,id PRIMARY 4 db.video_tag.video_id 1 Using where
EXPLAIN SELECT ...
;(请参考此处 - http://dev.mysql.com/doc/refman/5.0/en/explain.html)。欢迎提问!(并且在下一个问题时请尽可能提供更多信息) - ajreal