MySQL query to select based on item with highest votes

Question

I have a simple web app and when a user clicks favorite on an image the database stores a user_id and the image_id they were viewing, the table looks like this:

Favorites
---------------------
-user_id - image_id -
---------------------
-abc     - 123      -
-abc     - 456      -
-def     - 123      -
---------------------

I'm trying to find the top 10 favorite images (globally), that is the 10 images with the most favorites overall. The query simply needs to find the 10 image_id values that occur most frequently. So far I've tried a few things along the lines of

SELECT image_id, COUNT(*) FROM favourites GROUP BY image_id LIMIT 100 ORDER DESC

What would be the right query to accomplish this?

check this http://stackoverflow.com/questions/1831417/count-top-10-most-occuring-values-in-a-column-in-mysql?answertab=votes#tab-top hope its useful — nickle, Sep 03 '13 at 10:44
Yes that does also cover it thanks! My search terms were a little wrong it seems, I wasn't finding what I needed! — Scott Helme, Sep 03 '13 at 10:47

score 6 · Accepted Answer · edited May 23 '17 at 11:57

The following query should do the trick, it is almost the same as your code, but the last bit is different:

select
    image_id,
    count(*)
from
    favourites
group by
    image_id
order by
    count(*) desc
limit 10

You might also want to have a read of Q&A that I wrote which covers off a lot of stuff like this in a great deal of depth.

Edit:

To answer one of the comments below, does using count(*) in the order by statement cause it to calculate again?

No.

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)

You can see from this that it stores the data in temporary and uses it from there.

Fantastic, that sorted it! Just waiting for the time limit to accept the answer. Thanks! :-) — Scott Helme, Sep 03 '13 at 10:46
@ScottHelme Great, but seriously, do yourself a favour and read that article. I dare say after reading it, you can come back here and answer a good few of the SQL questions that pop up :) — Fluffeh, Sep 03 '13 at 10:47
order by count(*) desc i'm concerned about this line, will mysql map it to the previous calculated one or will it calculate it twice ? — trrrrrrm, Sep 03 '13 at 10:48
@ra_htial something like `COUNT(*) [Count]` then `ORDER BY [Count]` do you mean? — Scott Helme, Sep 03 '13 at 10:50
i mean if we use `select count(*)` and then `order by count(*)` what will mysql do ? calculate this once and apply it or it will be calculated twice ? — trrrrrrm, Sep 03 '13 at 10:54
Interesting, but I dare say that it won't make a difference. Let me have a quick look and post back. — Fluffeh, Sep 03 '13 at 10:55
@ra_htial There we go, looks like it doesn't calculate it over, it uses temp storage. — Fluffeh, Sep 03 '13 at 11:02

score 0 · Answer 2 · edited Sep 04 '13 at 10:05

0

Try this:

SELECT
  image_id, count(image_id)
FROM Favorites
GROUP BY image_id
ORDER BY 2 DESC
LIMIT 10

edited Sep 04 '13 at 10:05

Alexander Yancharuk

13,817
5
55
55

answered Sep 03 '13 at 10:47

trrrrrrm

11,362
25
85
130

MySQL query to select based on item with highest votes

2 Answers2