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

[Ronald Brown]

i removed few single field indexes and slow queries are runinng fine now , but previously fast queries are taking longer than 4 seconds now.

what is the work around for this ?

i mean one index works better for DESC while another Index works better for ASC ,and mysql cant choose the proper one .

how can i solve this issue.

eg.

25 rows in set (5.22 sec)

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_
key: enabled_seeders_upload_date_
key_len: 1
ref: NULL
rows: 3347495
Extra: Using index condition; Using where
1 row in set (0.00 sec)


previously it was fast, but i guess after i removed index seeders ,its slow.

but if i add index seeders , then another query gets slower.