How to organize the feed of posts?

Question

PHP

Hello, I really need your advice in terms of the proper organization of the database / storage for the feed of posts. The bottom line is this: you need to display all likened articles, everyone you subscribe to, but you need to group them so that the position of the article depends on the first like .... Now I’ll try to show this with an example.
We have users A,B,C. A follows B and C. Let users B and C like the same article. (First B then C). So in the feed, the position of this article should be by the first like, that is, by the time of like B.
First, two tables were made in MySQL:

1. user_relationships(idUser INT(11), idSub INT(11))
   
2. post_likes (id INT(11), idPost INT(11), idUser INT(11), size INT(11), date DATETIME)
   

The first is the relationship between users, the second is the likes ... And they chose this query:

SELECT  min(id) as id, idPost, posts_likes.idUser 
FROM `post_likes`,`users_relationships` WHERE 
    post_likes.idUser=users_relationships.idSub AND
     users_relationships.idUser=$user_id AND post_likes.size >= 0 
GROUP BY idPost ORDER BY id DESC

But with the filling of the post_likes table, the query time began to increase dramatically, I looked at explain, and it became clear that the query cannot use indexes... After looking at the muscle documentation, I realized that this is due to the fact that grouping and sorting are performed by different fields . Can you please suggest how this can be overcome?
Also, the idea arose to generate a tape in Redis, and, in general, completely transfer these two plates there. But there is also a problem with the generation of the feed... The structure in Redis is as follows: for each user, all his liked articles are stored. In order to generate a feed, we simply use the zunionstore for everyone the user follows, and then store the feed for the user. But here there is a problem with deleting articles if the user has unsubscribed (it’s too much to bypass), and with a lot of memory overrun. , and it may turn out that the post will appear twice ...)
I beg you to help me, how to do it right. Thanks in advance.

Answer 1

1) I can’t understand why you need id and size in the post_likes table. To register the fact and time of the like, this is enough: (idPost, idUser, date) with the key (idPost, idUser).
2) why ORDER BY id and not by date?
3) make the normal indexes you need, including the fields by which you filter and sort (indicating the sort order).
Under heavy load with such a task, it is better to try the graph database (when there are many connections, like you have with subscribers).