mysql - 改善非常慢的 'related items' 查询的最佳选项

Question

我有两个表，一个是图像，另一个是描述哪些图像被哪些用户喜欢了。第二个表每个图像/用户组合只有一行。我想编写一个查询，该查询将返回数据库中哪些其他图像具有尽可能多的共同“喜欢者”。所以，表格看起来像:

images
+----+----------+
| id | filename |
+----+----------+

images_users
+----------+---------+
| image_id | user_id |
+----------+---------+

我的查询是:

SELECT images.filename FROM 
  images_users INNER JOIN images ON images.id = images_users.image_id 
   WHERE images_users.user_id IN ( .... list of user IDs ....) 
   AND NOT images.id = <current image id>
  GROUP BY images.id ORDER BY COUNT(images.id) DESC LIMIT 10;

也就是说，找到所有匹配“喜欢者”的行，按图像 ID 分组，按计数排序，然后返回前 10 个。我还排除了当前图像 ID，因为那当然是否则总是 HitTest 门的。

当我想要匹配的用户 ID 列表增长时，这个查询可能需要几分钟才能运行 - 尽管我只在家用 PC 上运行它，而不是大型服务器。

我有点被困在这里的感觉，因为我的数据已根据规则规范化，但这对服务器来说只是一项艰巨的工作。我在连接和查询中的所有列上都有索引。

我能否以另一种方式表示此信息，这可能会缩短查询时间？

----- 针对以下问题进行编辑 -----

查询的解释输出如下:

explain SELECT filename  from images_users inner join images on id = image_id 
where image_users.user_id in
(162,591,596,603,856,1654,1967,2219,3640,3847,3956,9201,10601) 
and not images.image_id = 41578 group by images.id order by count(images.id) desc limit 10;

"1" "SIMPLE"    "images_users"  "range" "u_id_2,u_id,i_id"  "i_id"  "4" \N  "9086346"   "Using where; Using temporary; Using filesort"
"1" "SIMPLE"    "images"    "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "images_users.i_id" "1" ""

images_users 表中大约有 10,000 个用户、大约 150 万张图像和大约 600 万行。

Answer 1

图形数据库就是答案

我在这里回答我自己的问题，因为我接受了@Jason 的评论，点击了指向 neo4j 的链接，安装了它，学习了一些它出色的查询语言 Cyper 和 bam。问题解决了。

我把我所有的数据，200 万张图片和 1000 万张关系，插入了 neo4j。首先，我创建了索引。

CREATE INDEX ON :Image(filename)
CREATE INDEX ON :User(name)

然后针对用户和图像之间的每个关系运行以下查询。这还会为用户和图像创建节点(如果它们尚不存在)。

MERGE (i:Image{filename:'...'})
MERGE (u:User{name:'...'})
CREATE (u)-[:LIKES]->(i)

这有点慢，我最终确实稍微改进了导入过程，但结果数据是一样的。然后搜索的查询变为

MATCH (n:Image{filename:'...'})<-[]-(u:User)-[l]->(i:Image) 
WITH count(l) AS c, i.filename AS f 
ORDER BY c DESC LIMIT 10

在任何情况下，这都是一种更具表现力的查询编写方式，并且运行速度比等效的 MySQL 查询快一个数量级，在 MySQL 查询上存在所有适当的索引。

其他查询也将以非常昂贵的联接结束，例如向我显示所有超过五个喜欢的图像，非常有效

MATCH (n:Image) where size ( (n)<-[]-() ) > 3 return n.filename 
LIMIT 20