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

Question

Running this with ORDER BY takes more than 10 seconds and my site crashes with 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 only a couple of seconds.

 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 is EXPLAIN

Any ideas? I am open to splitting it into multiple queries, creating temporary tables or something else that will help. This request listens to me (and my users).

Thanks!

Answer 1

You probably need an index in the rating column.

Answer 2

OK, first at first: LIMIT hides a lot of bad queries, until someone adds ORDER BY. LIMIT at the invitation to the DB engine will start the query as soon as the specified number of records are created, but as soon as ORDER BY is added, ALL records are created inside, but hidden from the programmer - if the LIMIT'd query slows down ORDER BY very much, it was not a very good query to start.

However, there are a few small changes that you can make to your query (and database settings) to improve the situation. From looking at the EXPLAIN plan (you are in the top 10%, including this), a lot of things stand out - in the result set, 240,000 entries are collected. From "Using Filesort" it seems like a 2-way sorting scene is happening, plus the query creates a temporary table - I would look at an increase in your sort_buffer_size , but be careful to make it too large, as I seem to recall that it is not a global buffer, so don't make it 256 MB if you have 100 simultaneous sessions - I would suggest that 4 MB or 8 MB might be good starting points.

If this does not improve the situation, I would start working on the query itself: the EXPLAIN output tells us that the lcase_influence index has 300 + bytes of keys - if you move the influence string to a separate tblInfluence, and just include tblInfluence.id in the tbluserinfluences table and index it, then you will both take off the size of the tbluserinfluences and index.infame tables.

If this does not fix the problem, I would look at moving the sort so that it only sorts the minimum fields, and not the entire output record. I also joined tblUsrContent directly with tbluserinfluences - I suspect that would not have much effect, but if this were my code, I would prefer one-step joins for long join chains where possible.

Answer 3

Well, this is a huge hack, but I solved a (temporary) solution to the problem.

The query is slow when searching for very popular bands such as pink floyd up to coldplay. Any group is less popular, so the query is fast.

Through some trial error, I found that if I click on the tblcontent.score index request , it is super-fast for popular groups such as pink floyd, but then slow for less popular groups such as romance.

Hacky solution: Strength rating index for the top 100 ranges. Let MySql use its default values ​​for all other groups. Sigh.

So the quick version of the pink floyd request is:

 select * from tbluserinfluences, tblcontent FORCE INDEX(score), 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 

And a quick version of the romantic (less popular) request:

 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 

This is a decent solution while I am in Defcon 5. I will find out something more elegant later.

Answer 4

Without seeing your schema, I would add an index to the SCORE field. With any index, there will be a slight performance hit on INSERT, but it sounds like a selective query is the most important part for you.

Answer 5

try it

 select ui.*, tc.* tu.* from tbluserinfluences as ui LEFT JOIN tblusers AS tu ON tu.id = ui.userid LEFT JOIN tblcontent AS tc ON tc.userid = tu.id where ui.lcase_influence = 'pink floyd' order by ???.score desc limit 0, 160 

replace ??? with the corresponding table. I can’t try, but I will start with this.

Answer 6

change my.ini file here: innodb_buffer_pool_size = 300M - and change the size depending on the memory you have on your PC or server. It worked for me!

Answer 7

this is how i do it

 select * from ( select * from tbluserinfluences, tblcontent FORCE INDEX(score), tblusers where tblcontent.userid = tblusers.id and tbluserinfluences.userid = tblusers.id and tbluserinfluences.lcase_influence = 'pink floyd' limit 0, 160) tbl order by tbl.score desc 

First restriction, and then sort only 160 records instead of sorting, and then restriction