我有一个简单的web应用程序，当用户点击图像上的收藏夹时，数据库会存储一个user_id和他们正在查看的image_id，表如下所示：
Favorites
---------------------
-user_id - image_id -
---------------------
-abc - 123 -
-abc - 456 -
-def - 123 -
---------------------
我正在努力寻找（全球）最受欢迎的10张图片，也就是说，总的来说，这10张图片最受欢迎。查询只需要找到最频繁出现的10个image_id值。到目前为止，我已经尝试了一些类似的方法
SELECT image_id, COUNT(*) FROM favourites GROUP BY image_id LIMIT 100 ORDER DESC
实现这一点的正确查询是什么？
下面的查询应该可以完成任务，它与您的代码几乎相同，但最后一位不同：
select
image_id,
count(*)
from
favourites
group by
image_id
order by
count(*) desc
limit 10
你可能还想读一读Q&我写的一篇文章，非常深入地涵盖了很多这样的东西。
编辑：
要回答以下注释之一，在order by语句中使用count(*)是否会导致它再次计算？
没有。
mysql> select * from test2;
+------+-------+-------+
| id | barry | third |
+------+-------+-------+
| 1 | ccc | NULL |
| NULL | d | 1 |
| NULL | d | 2 |
| NULL | d | 3 |
+------+-------+-------+
4 rows in set (0.00 sec)
mysql> explain select barry, max(third) from test2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.11 sec)
mysql> explain select barry, max(third) from test2 order by barry;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain select barry, max(third) from test2 order by max(third);
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 4 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)
您可以从中看到，它将数据存储在temporary中，并在那里使用它。
试试这个：
SELECT
image_id, count(image_id)
FROM Favorites
GROUP BY image_id
ORDER BY 2 DESC
LIMIT 10