MySQL Forums
Forum List  »  Performance

Re: Why is ORDER BY ASC is slow but ORDER DESC is fast ? and how can i fix it ?
Posted by: Ronald Brown
Date: October 06, 2015 11:50PM

Rick James Wrote:
-------------------------------------------------------
> As for your ASC/DESC question -- there is no
> 'good' answer.

what if i create index for each and every execution plan and force mysql to use specific query depending on the fields used in the query .

is this a good idea ?


is the slowdown causing becuase of majority rows contains 0 in the ORDER BY field ?


> As for that query, it needs either of these:
> INDEX(enabled, verified, category)
> INDEX(verified, enabled, category)


i have tried creating both but query is ignoring both of them

mysql> explain SELECT content.title, content.og_name, content.record_num, content.category, content.upload_date, content.hash, content.comments_count, content.verified, content.uploader, content.size, content.seeders, content.leechers FROM content WHERE enabled = 1 AND category IN (100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120) AND verified = 1 ORDER BY seeders ASC LIMIT 7300,25\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: content
type: range
possible_keys: enabled_category_upload_date_verified_,enabled_upload_date_verified_,enabled_category_verified_,enabled_verified_,enabled_uploader_,enabled_seeders_upload_date_,enabled_verified_category,verified_enabled_category
key: enabled_seeders_upload_date_
key_len: 1
ref: NULL
rows: 3349796
Extra: Using index condition; Using where
1 row in set (0.00 sec)




thanks & regards.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Why is ORDER BY ASC is slow but ORDER DESC is fast ? and how can i fix it ?
2090
October 06, 2015 11:50PM


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.