Ranking help, database examines all rows due to no index needed...

[krispykreme]

"Select * from user order by posts DESC limit 0, 25";

 

 

So I want to display the top 25 users that have the most posts....

 

when i run this query right now it looks at 30,000+ rows (every one) and this information was found out using explain if you need more information from explain let me know...

 

is there a way to have the database constantly sort by posts DESC so it doesnt have to examine every row?

 

Basically, I want a ranking page, that splits up all the users onto 25 users per page arranged with the person that has the most posts at #1

 

but right now when you do page one it scans the whole database, then page 2 it scans the whole database again!

 

ive been stuck on this one for awhile, ive been able to figure out other things for indexing but this one i cannot

 

Why can't the database actively sort the database by networth as an update is made and use that?

 

I have other questions about database types like myisam and innob but ill save those after i get this nasty query gone....right now every time someone views this page it runs this and scans 30k rows and only shows 25 of them since i have them paged like that so it is not effective...

[fenway]

Index order can't get specified yet... in principle, you can physically alter the table with a given ordering, but that won't help as soon as your table changes...

[krispykreme]

so it is impossible to get the ranks to update live while not having to examine all the rows in a table...

 

what if it wasnt live and I specified a rank 1-30000 for all users in the table

 

and i used a between statement that was flexible based on the page?  and the index of the 'rank' was such that no other user had the same rank?  then I could run a cron job to scan and assign ranks every 30 seconds or 1min?

 

hmmm

[fenway]

Sorry, perhaps I misread your post -- does the EXPLAIN show "using filesort" or not?  The 30k+ rows doesn't mean anything, it doesn't take LIMIT into account. 

[krispykreme]

mysql> EXPLAIN SELECT username from users order by posts DESC limit 0,25;

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

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

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

|  1  | SIMPLE        | users  | index  | NULL            | posts      |      8    | NULL | 31240  |          |

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

1 row in set (0.00 sec)

 

 

doesnt look like it used filesort that time at least

[fenway]

This is as good as you can get -- you're using the index, no filesort... "rows" doesn't account for limit, as mentioned above.  For comparison, drop the index on posts, and you'll see how long it actually would take to order 30K rows backwards! ;-)