How to implement a news feed for a social network?

Question

PHP

Hello! I'm trying to implement my small social. network (for myself, well, just training).
The problem arose almost on the most important thing - the output in the feed of all the records of friends and groups.
There were no problems with friends, but when it was also necessary to display records from the groups in which this user is a member, the matter suddenly stalled ...
What I have from the tables:
comments - directly all the records of friends and groups.
users - data of all users and groups (I decided to write users and groups in one table in order to join less).
friends - data of friends (who added whom). Accordingly, we display the records of only my friends.
groups_members - group data (who is in which group).
There is a column in the commentsuser_id is the id of the user who posted this post, and post_id is the id of the user for whom this post was posted, or the group id if the post was made on a group wall.
Here it is impossible to join all this, then one record is displayed, then everything is mixed up at once ...
So help would not hurt.
Thanks in advance.

Answer 1

Well, you can join this thing (I suspect that you will also have to fill it with a union), but this is usually not done, because this solution will then be impossible to scale. The request is heavy, caching will help badly, since the activity is large and the cache will often be reset and the database will twitch a little less, well, if the database needs to be split into 2 or more servers, then we will remain to suck our paw with joins.
To begin with, you can do this - for a specific `user_id` (the owner of the news feed):
1) With the first request, we pull the ids of all friends
2) With the second request, we pull the ids of all groups that it belongs to
3) With the third request, we pull all WHERE `user_id` records from comments IN (groups + friends) ORDER BY/LIMIT to taste.
Advantages:
Less load on the database, it will do less expensive operations with temporary tables. On indexes faster will give result.
The first 2 requests can be safely cached, since friends are added / removed much less often than the posts themselves. Therefore, in fact, everything comes down to one request for the posts themselves without joins.
Cons:
More code for processing 3 requests. A slightly longer script execution, which, in principle, will not be felt by the user, and in general will not be fair if the first 2 requests are cached.
But if the task is to scale, then this approach to business is not enough. There, it will most likely be necessary to sacrifice extra space in order to reduce the cost of collecting spread data across all database servers, for example, to maintain a separate newsfeed table for each user, which will aggregate all the necessary IDs.
UPD: ...as @jarvis pointed out