How to make a MySQL query from a query?

Question

MySQL

Hello! I have a rather difficult task, which I have already broken my head.

There is a table friends
id | friend_id | user_id
1 | 1 | 2
2 | 1 | 10
3 | 1 | 50

There is a table posts
id | user_id | text | type
1 | 1 | test | 1
2 | 10 | test | 1
3 | 50 | test | 1
4 | 1 | test | 4
5 | 2 | test | 1

The task is this: It is necessary to display from the post table only those records with user_id, the user_id of which will be equal to user_id or friend_id, in turn, user_id or friend_id must correspond, for example, to '1'. And then group all this by post.id and sort by post.id. With all this, display no more than 10 recent records (last by post.id).

The way I'm trying is:
SELECT * FROM friends INNER JOIN posts ON (friends.user_id = '1' AND posts.user_id = friends.friend_id AND posts.type != '4') OR (friends.friend_id = '1' AND posts. user_id = friends.user_id AND posts.type != '4') OR (posts.user_id = '1' AND posts.type != '4') GROUP BY posts.id ORDER BY posts.id DESC LIMIT 0, 10

Works as expected, but if there are more than 30,000 records in the post table, then the query takes a very long time to complete. The more entries in the post, the longer.

I am more than sure that there is a catastrophic error in the request that is causing everything to run so slowly. Can anyone help find the bug or maybe even suggest a new request? I will be most grateful ♥

Answer 1

It is not clear how many user_id on average match the condition

user_id which will be equal to user_id or friend_id, in turn, user_id or friend_id must match for example '1'.

I don't know much about the optimizer in mysql, but I don't think it's very smart, so the simpler the query, the better. Try something like this:

SELECT id FROM posts
 WHERE user_id IN (
    SELECT '1'
     UNION
    SELECT user_id FROM friends WHERE friend_id = '1'
     UNION
    SELECT friend_id FROM friends WHERE user_id = '1'
)
ORDER BY id DESC LIMIT 0, 10

And, of course, there should be an index on posts.user_id.