MySQL: any way to turn these N queries into fewer queries?

Question

I have a list user_id of N integers, e.g.

[1001, 1023, 13452, 1679834, ...]

and a table:

CREATE TABLE content (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  content VARCHAR(100),
  score INT
);

I need to take those N integers from user_id and for each user_id get the top 3 content that has the highest score. So basically I need to run this query N times:

SELECT *
FROM content
WHERE user_id=1001
ORDER BY score DESC
LIMIT 3;

N could be a very large number. So I'd very much want to avoid running those queries one-by-one.

Is there any way to reduce the number of queries I need to run? Some sort of bulk select perhaps?

Answer 1

This should work:

$str_ids = implode(', ', $arr_ids);

SELECT id, user_id, content, score
FROM (  SELECT *, (@rownum := @rownum + 1) AS rownum, 
            case when @user_id IS NULL then @user_id := c.user_id when @user_id != c.user_id then CONCAT(@rownum := 0, @user_id := c.user_id) AS dummy_value
        FROM (  SELECT *
                FROM content
                WHERE user_id IN ({$str_ids})
                ORDER BY user_id ASC, score DESC) AS c, (@rownum := 1, @user_id := NULL) AS vars
        HAVING rownum <= 3

Maybe there's a better way to do this. If such; let me know!