Re: Why is ORDER BY ASC is slow but ORDER DESC is fast ? and how can i fix it ?

[Ronald Brown]

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.