MySQL Forums
Forum List  »  Performance

Why is ORDER BY ASC is slow but ORDER DESC is fast ? and how can i fix it ?
Posted by: Ronald Brown
Date: October 03, 2015 12:47AM

i have queries which works faster i.e. in 0.xx seconds for ORDER BY field DESC
but if i use
ORDER BY field ASC , it gets slow e.g. 7.xx seconds

why is that ?
what is causing this to happen ?
ORDER BY field contains majority 0 (zero). is it cauing this slowdown ?

some example queries are

# Query_time: 12
SELECT * FROM content WHERE enabled = 1 AND category IN (600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620) AND upload_date >= '2015-08-31' ORDER BY comments_count ASC LIMIT 900,25


# Query_time: 16.470
SELECT * FROM content WHERE enabled = 1 AND upload_date >= '2015-09-23' AND verified = 1 ORDER BY vote_up ASC LIMIT 525,25


# Query_time: 10.503 s
SELECT * FROM content WHERE enabled = 1 AND upload_date >= '2014-09-30' ORDER BY seeders ASC LIMIT 450,25

i have website where i offer to sort results by various means
e.g.
upload date DESC
upload date ASC
size DESC
size ASC
comments count DESC
commnets count ASC
upvotes count DESC
upvotes count ASC
and some more sort by options

to lower the load on mysql i only allow surfers to go upto 10000 results set,

how can i improve the mysql performance ?

thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Why is ORDER BY ASC is slow but ORDER DESC is fast ? and how can i fix it ?
2169
October 03, 2015 12:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.