ORDER BY makes my query super slow. Examples inside. Any ideas for speeding it up?
mysql, performance, sql, sql-order-by
Solution
You likely need an index on the score column.
Problem
Running this with the ORDER BY takes over 10 seconds and ends up crashing my site when there's high traffic. ``` select * from tbluserinfluences, tblcontent, tblusers where tblcontent.userid = tblusers.id and tbluserinfluences.userid = tblusers.id and tbluserinfluences.lcase_influence = 'pink floyd' order by tblcontent.score desc limit 0, 160 ``` Running the same query without ORDER BY takes just a couple miliseconds. ``` select * from tbluserinfluences, tblcontent, tblusers where tblcontent.userid = tblusers.id and tbluserinfluences.userid = tblusers.id and tbluserinfluences.lcase_influence = 'pink floyd' order by tblcontent.score desc limit 0, 160 ``` Here's the EXPLAIN Any ideas? I'm open to splitting it into multiple queries, creating temporary tables, or anything else that will help. This query is bugging the heck out of me (and my users). Thanks!