ORDER BY makes my query super slow. Examples inside. Any ideas for speeding it up?

Question

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!

Answer 1

You likely need an index on the score column.

Answer 2

OK, first things first: LIMIT hides a multitude of bad queries, right up until someone adds an ORDER BY - the LIMIT is in invitation to the DB engine to bail the query as soon as the specified number of records are generated, but as soon as an ORDER BY is added, ALL the records are internally generated, but hidden from the programmer - if a LIMIT'd query is slowed down greatly by an ORDER BY, it wasn't a good query to start with.

That said, there are a bunch of small changes to make to your query (and the DB settings) to improve things. From looking at the EXPLAIN plan (you're in the top 10% by including this) a bunch of things stand out - there are 240,000 records in the result set being sorted. From the 'Using Filesort' it looks like there's a 2-pass sort stage happening, plus the query is creating a temporary table - I'd look at increasing your sort_buffer_size, but be wary of making this too big, as I seem to recall it's per-session not a global buffer, so don't make it 256MB if you have 100 concurrent sessions - I'd guess 4MB or 8MB might be good starting positions.

If this doesn't improve things greatly I'd start working on the query itself: the EXPLAIN output tells us that the lcase_influence index has 300+ byte keys - if you move the influence string out to a separate tblInfluence, and just include the tblInfluence.id in the tbluserinfluences table, and index this then you will both drop the size of the tbluserinfluences table and the influencename-index.

If this doesn't fix the problem, then I'd look at moving the sort so that it only sorts the minimum fields required, not the entire output record. I'd also join tblUsrContent directly to tblUserInfluences - I suspect that it wouldn't make much of a difference, but if it was my code, I'd prefer single-step joins to long chains of joins where possible.