Serious MySQL Performance Issue (Joins, Temporary Table, Filesort....)

Question

I've got a users table and a votes table. The votes table stores votes toward other users. And for better or worse, a single row in the votes table, stores the votes in both directions between the two users.

Now, the problem is when I wanna list for example all people someone has voted on.

I'm no MySQL expert, but from what I've figured out, thanks to the OR condition in the join statement, it needs to look through the whole users table (currently +44,000 rows), and it creates a temporary table to do so.

Currently, the bellow query takes about two minutes, yes, two minutes to complete. If I remove the OR condition, and everything after it in the join statement, it runs in less than half a second, as it only needs to look through about 17 of the 44,000 user rows (explain ftw!).

The bellow example, the user ID is 9834, and I'm trying to fetch his/her own no votes, and join the info from user who was voted on to the result.

Is there a better, and faster way to do this query? Or should I restructure the tables? I seriously hope it can be fixed by modifying the query, cause there's already a lot of users (+44,000), and votes (+130,000) in the tables, which I'd 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

Instead of the OR, you could do a UNION of 2 queries. I have known instances where this is an order of magnitude faster in at least one other DBMS, and I'm guessing MySQL's query optimizer may share the same "feature".

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

You've answered your own question: yes, you should redesign the table, as it's not working for you. It's too slow, and requires overly complicated queries. Fortunately, migrating the data is just a matter of doing essentially the query you're asking about here, but for all user instead of just one. (That is, a sum or count over the unions the first answering suggested.)