Mysql: get 2 results from each cat [from one to a large ratio]

Question

I am looking for the best way to get a result with a database containing over 100,000 messages and over 100,000 cats

Here are my tables

Cats

----------------- - id | name | ----------------- - 1 | x | ----------------- - 2 | y | ----------------- 

Message

 -------------------------------------- - id | cat_id | title | content | -------------------------------------- - 1 | 1 | Post 1 | .. . . .| -------------------------------------- - 2 | 1 | Post 2 | . . . . .| -------------------------------------- - 3 | 2 | Post 3 | .. . . .| -------------------------------------- - 4 | 1 | Post 4 | . . . . .| -------------------------------------- - 5 | 1 | Post 5 | .. . . .| -------------------------------------- - 6 | 2 | Post 6 | . . . . .| -------------------------------------- - 7 | 1 | Post 7 | .. . . .| -------------------------------------- - 8 | 2 | Post 8 | . . . . .| -------------------------------------- 

Here is the result I want to get

Result

 -------------------------------------- -Postid | cat_id | title | content | -------------------------------------- - 1 | 1 | Post 1 | .. . . .| -------------------------------------- - 2 | 1 | Post 2 | . . . . .| -------------------------------------- - 3 | 2 | Post 3 | .. . . .| -------------------------------------- - 6 | 2 | Post 4 | . . . . .| -------------------------------------- 

Here is the query that I am just writing, but I'm looking for the best query

 SELECT * From post WHERE posts.cat_id = 1 limit 2 UNION SELECT * From post WHERE posts.cat_id = 2 limit 2 

What happens if I want to get from 10 cats in one request

Answer 1

 set @i := 0, @cat_id = 0; select post.id as Postid, cat_id, title, content from post inner join ( select id, case when @cat_id = cat_id then @i := @i + 1 else @i := 1 end as i, case when @cat_id != cat_id then @cat_id := cat_id end from ( select id, cat_id from post -- where cat_id in (1, 2) uncomment this to limit categories order by cat_id ) a ) s on s.id = post.id where i <= 2 order by cat_id, post.id 

The title of the question says that each category and question say 10 categories, so I commented on the where clause to make it optional.

Answer 2

For best performance, use the exists clause.

 select * from posts p where exists (select c.id from cats c) 

Check sqlfiddle .