Jump to content


Photo

Order By Help


  • Please log in to reply
5 replies to this topic

#1 BigDummy

BigDummy
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 06 May 2003 - 06:59 PM

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?

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 08 May 2003 - 10:44 AM

Are you using an index on your stats table ?

P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 08 May 2003 - 10:50 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#4 BigDummy

BigDummy
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 May 2003 - 05:11 PM

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.

#5 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 13 May 2003 - 09:23 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#6 BigDummy

BigDummy
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 13 May 2003 - 02:38 PM

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

Thanks very much for your suggestions.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users