One to many -- Select 3 subitems for every item.

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I have two tables with a one to many relationship:

collection: id, user_id, title, description, item_count
items: id, collection_id, title, description

Now, I want to display the collections on a result page. (paginated). But, for each collection, I want to show the first 3 or so items that are in it.

What is the best possible way to do this efficiently? If I just do a straight up join I get the collection information 3 times as well, which I do not need.

Or should I just do a second query after having queried for the collections first? (Then using the collection_ids to get the items)?

What I do now:

SELECT c.id, c.title, c.description, GROUP_CONCAT(i.id) AS item_ids FROM collections c INNER JOIN items i ON i.collection_id = c.id ORDER by c.created_at DESC LIMIT 10

Then i collect those item_ids in php and do a “WHERE id IN (…)” on all those ids.

But I need to know if there is a better solution.

Thank you for your help,
Wesley

2

I think that is what I would start with. Pagination is often a serious performance problem, so full results can be very inefficient.