I have got a customers table along with a votes table. The votes table stores votes toward other customers. As well as for better or worse, just one row within the votes table, stores the votes both in directions between your two customers.
Now, the issue is after i wanna list for instance everybody someone has chosen on.
I am no MySQL expert, but from what I have determined, because of the OR symptom in the join statement, it must examine the whole customers table (presently +44,000 rows), also it produces a brief table to do this.
Presently, the bellow query takes about two minutes, yes, two minutes to accomplish. Basically take away the OR condition, and everything after it within the join statement, it runs in under half another, because it only must examine about 17 from the 44,000 user rows (explain ftw!).
The bellow example, the consumer ID is 9834, and I am attempting to fetch his/her very own no votes, and join the data from user who had been chosen onto the end result.
It is possible to better, and faster method of doing this question? Or must i restructure the tables? I seriously hope it may be fixed by modifying the query, cause there's already lots of customers (+44,000), and votes (+130,000) within the tables, which I'd need to migrate.
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
Rather than the OR, you can perform a UNION of two queries. I've known instances where it is really an order of magnitude faster in a minumum of one other DBMS, and I am speculating MySQL's query optimizer may share exactly 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
You've clarified your personal question: yes, you need to redesign the table, as it is not on your side. It's not fast enough, as well as excessively complicated queries. Fortunately, moving the information is only a matter of doing basically the query you are asking about here, however for all user rather than only one. (That's, a quantity or count within the unions the very first responding to recommended.)