MySQL - How to limit query results based on foreign key

Question

I have a users table, a posts table, and a search feature that queries MySQL for posts.

I want it to return all posts for 100 specific users (with a maximum of 6 posts per user).

I have a column called user_id on the posts table which is a foreign key.

Right now I can return all posts for those users like this:

SELECT id FROM posts WHERE user_id IN (1,2,3,4 .... 98,99,100);

However many of these users have hundreds of posts. I want to cap it at 6 per user. How can I add such a constraint to my query?

I am using MySQL version 8.0.15

@MariosNikolaou Thank you but I don't think you understood my question correctly. I know how to limit the total number of posts using `LIMIT`. I want to limit the number of posts per user to 6. — Symphony0084, Dec 29 '19 at 08:01

Lukasz Szozda · Accepted Answer · 2019-12-29T08:00:42.033

4

ROW_NUMBER(MySQL 8.0+) could be used:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY col_time DESC) AS rn
                                                   -- taking the newest posts
  FROM posts 
  WHERE user_id IN (1,2,3,4 .... 98,99,100)
) sub
WHERE rn <= 6;

edited Dec 29 '19 at 08:00

answered Dec 29 '19 at 07:55

Lukasz Szozda

162,964
23
234
275

1

Waiting another minute to accept this as the correct answer, but this worked like a charm in no time flat. Perfect! – Symphony0084 Dec 29 '19 at 08:06
1

This brought my query time down to 0.5 seconds from about ~12 seconds – Symphony0084 Dec 29 '19 at 08:24

score 1 · Answer 2 · answered Dec 29 '19 at 14:08

Lukasz's answer is fine. But, you don't need to use row_number():

SELECT p.*
FROM posts p
WHERE p.user_id IN (1,2,3,4 .... 98,99,100) AND
      p.col_time >= ALL (SELECT p2.col_time
                         FROM posts p2
                         WHERE p2.user_id = p.user_id
                         ORDER BY p2.col_time DESC
                         LIMIT 1 OFFSET 5
                        );

(Note: The ALL handles the case when there are fewer than 6 posts for the user.)

With an index on posts(user_id, col_time), it would be interesting to know if this is faster or slower than ROW_NUMBER().

MySQL - How to limit query results based on foreign key

2 Answers2