Order By Help

[BigDummy]

I cannot seem to optimize this query.

 

SELECT username,rank FROM stats LEFT JOIN member ON memberid=member.id ORDER BY rank DESC LIMIT 10

 

where username is in the member table and rank is in the stats table. It is taking much longer than I know it should. Here is the EXPLAIN of the query:

 

 

+--------+--------+---------------+---------+---------+----------------+-------+----------------+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+--------+--------+---------------+---------+---------+----------------+-------+----------------+

| stats | ALL | NULL | NULL | NULL | NULL | 49999 | Using filesort |

| member | eq_ref | PRIMARY | PRIMARY | 3 | stats.memberid | 1 | |

+--------+--------+---------------+---------+---------+----------------+-------+----------------+

 

It seems that it is doing the JOIN first, and then the ORDER and LIMIT. I say this because if I add a where clause like \"WHERE rank > 10000\" it is almost instanteous. Unfortunately I will not always know what number to use in place of 10000.

 

According to the MySQL documentation, this query should use the index for sorting instead of the file sort, which it does if I remove the join as in:

 

 

desc SELECT rank FROM stats ORDER BY rank DESC LIMIT 10;

+-------+-------+---------------+------+---------+------+-------+-------------+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+-------+-------+---------------+------+---------+------+-------+-------------+

| stats | index | NULL | rank | 10 | NULL | 49999 | Using index |

+-------+-------+---------------+------+---------+------+-------+-------------+

 

which is also instanteous. Can anyone help me optimize this?

[pallevillesen]

Are you using an index on your stats table ?

 

P.

[pallevillesen]

Thinking a little...

 

Since you\'re using the order by, you\'re forcing mysql to do the full join first...

 

It would be better to do the subselect in the ranks first (quick, if you\'re indexing the rank coloumn).

 

Then join returned 10 id\'s to the members... Voila.

 

I\'m not sure if this is possible in one statement, but using temporary tables (which I\'m a big fan of) it should be no problem...

 

Greetings,

P.

[BigDummy]

Rank is indexed in the stats table, and so is memberid, along with member.id in the member table.

 

I had thought about breaking it down like you mentioned, and I think that is what I will do. Still, I do things like this quite frequently, and I would love to know how to speed it up in a single query.

 

Thanks again.

[pallevillesen]

I\'m not sure if you\'re still reading this.

 

Anyway, for things like this \"compiling\" top rankings etc. The best solution is probably to write an update script, which runs automatically every two hours or so... creating small tables with the top 10 ranks etc...

 

Compiling this info each time it is requested (say on a web page) is probably too timeconsuming... Better to have a slight \"update delay\"...

 

My 10 cents. EOD from my side, hope things work.

 

P.

[BigDummy]

That is exactly what I will do. I really should have thought of that. Hence my username.

 

Thanks very much for your suggestions.