Select from 4 categories using LIMIT

Databases
1

Hello

Suppose I have a forum app, like vbulletin. I would store the threads in THREADS table and THREADS table will have forum_id field that would tie it with the FORUMS table.

Now I want to display 4 blocks on home page, each block is the latest 10 posts from 4 different forums.

I also want to select forum titles, so I can use them in block titles. the forum titles are store in FORUMS table, of cause.

Is there a way to select the 40 rows from THREADS table in one SQL select for that?

2

The UNION of 4 queries, which each join the threads and forums tables, ordered by date with a limit of 10.

3

Thanks. It does look like UNION is the best way to go. I wonder if its fast. I mean, as far as performance is concerned, is this faster than doing 4 individual selects separately?

4

It will take about the same time on the database, but you won’t have four round-trips between your web server and database server, so the communication overhead is reduced significantly.