MySQL 比较、计数和排序依据

Question

我在理解如何解决一个看似简单的排序结果问题时遇到了麻烦。

我想比较有多少其他用户喜欢与ID 1 相同的水果，这是一个匹配最多的计数，并按降序显示结果。

用户：

1  jack
2  john
3  jim

水果：

id, title
1   apple
2   banana
3   orange
4   pear
5   mango

关系：2个索引（user_id，fruit_id）和（fruit_id，user_id）

user_id, fruit_id
1        1
1        2
1        5
2        1
2        2
2        4
3        3
3        1

预期结果： （与杰克最喜欢的水果相比 （ user_id=1 ））

user_id,  count
1         3
2         2
3         1

查询：

SELECT user_id, COUNT(*) AS count FROM relations
WHERE fruit_id IN (SELECT fruit_id  FROM relations WHERE user_id=1) 
GROUP BY user_id
HAVING count>=2

更多"优化"查询：

SELECT user_id, COUNT(*) AS count FROM relations r
WHERE EXISTS (SELECT 1 FROM relations WHERE user_id=1 and r.fruit_id=fruit_id)
GROUP BY user_id
HAVING count>=2

2 是最小匹配数。（以后需要）

解释：

id  select_type         table       type    possible_keys   key     key_len     ref                 rows    Extra
1   PRIMARY             r           index   NULL            uid     8           NULL                15      Using where; Using index
2   DEPENDENT SUBQUERY  relations   eq_ref  xox,uid         xox     8           r.relations,const   1       Using where; Using index

一切正常，直到我尝试使用ORDER BY count DESC

然后我明白了：使用临时的;使用文件排序

我不想使用临时表或文件排序。因为将来数据库应该处于高负载状态。

我知道，这就是SQL的定义和运作方式。但是我不知道如何以其他方式做到这一点？没有临时表和文件排序。

我需要首先向用户显示谁拥有最多的匹配项。

请帮帮我。

更新：

我用沃克·法罗（Walker Farrow）的查询做了一些测试（它仍然使用文件排序）。

20,000 rows -  avg  0.05 seconds
120,000             0.20 sec.
1,100,000           2.9 sec.

令人失望的结果。

可以

更改表结构，但是，通过这样的计数和排序 - 我不知道如何。

对如何做到这一点有什么建议吗？

Answer 1

可能最好的方法是创建一个子查询，然后在外部查询中排序，如下所示：

select *
from (
     SELECT user_id, COUNT(*) AS count FROM relations r
     WHERE EXISTS (SELECT 1 FROM relations WHERE user_id=1 and r.fruit_id=fruit_id)
     GROUP BY user_id
     HAVING count(*)>=2
     ) x
order by count desc

另外，我不知道为什么您需要添加exists.你能说以下几点吗：

select *
from (
     SELECT user_id, COUNT(*) AS count FROM relations r
     WHERE user_id=1 
     GROUP BY user_id
     HAVING count(*)>=2
     ) x
order by count desc

？

我

不确定，也许我错过了什么。呵呵，有帮助！