krispykreme Posted January 8, 2008 Share Posted January 8, 2008 "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... Quote Link to comment Share on other sites More sharing options...
fenway Posted January 8, 2008 Share Posted January 8, 2008 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... Quote Link to comment Share on other sites More sharing options...
krispykreme Posted January 8, 2008 Author Share Posted January 8, 2008 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted January 8, 2008 Share Posted January 8, 2008 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. Quote Link to comment Share on other sites More sharing options...
krispykreme Posted January 9, 2008 Author Share Posted January 9, 2008 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted January 9, 2008 Share Posted January 9, 2008 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! ;-) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.