Serious MySQL performance error (merge, temporary table, file ...)

Question

I have a user table and a vote table. The votes table stores votes in relation to other users. And for better or worse, a single row in the vote table stores votes in both directions between two users.

Now the problem is when I want to list, for example, all the people who have voted.

I am not a MySQL expert, but from what I found out, thanks to the OR condition in the join operator, it should look at the entire user table (currently +44,000 rows) and create a temporary table for this.

Currently, the next request takes about two minutes, yes, two minutes . If I delete the OR condition and everything after it in the join operator, it works in less than half a second, since it only needs to look at about 17 out of 44,000 user rows (explain ftw!).

Below is an example, the user ID is 9834, and I try to get his own no votes and join the information from the user who voted for the result.

Is there a better and faster way to make this request? Or do I need to rebuild the tables? I really hope that this can be fixed by modifying the query, because there are already a lot of users (+44,000) and votes (+130,000) in the tables that I would have to migrate.

thanks:)

SELECT *, votes.id as vote_id 
FROM `votes` 
LEFT JOIN users ON (
  (
    votes.user_id_1 = 9834
    AND
    users.uid = votes.user_id_2
  )
  OR
  (
    votes.user_id_2 = 9834
    AND
    users.uid = votes.user_id_1
  )
)
WHERE (
  (
    votes.user_id_1 = 9834
    AND
    votes.vote_1 = 0
  )
  OR
  (
    votes.user_id_2 = 9834
    AND
    votes.vote_2 = 0
  )
)
ORDER BY votes.updated_at DESC
LIMIT 0, 10

Answer 1

OR UNION 2 . , , , , , MySQL "".

SELECT  whatever
FROM    votes v
        INNER JOIN
                users u
                ON v.user_id_1 = u.uid
WHERE   v.user_id_2 = 9834
AND     v.votes_2 = 0

UNION

SELECT  whatever
FROM    votes v
        INNER JOIN
                users u
                ON v.user_id_2 = u.uid
WHERE   v.user_id_1 = 9834
AND     v.votes_1 = 0

ORDER BY updated_at DESC

Answer 2

: , , . . , - , , , . ( , , .)