i have two simple queries using order by clause and and I am trying to figure out how can i prevent filesort in query execution.
Query 1# SELECT * FROM ad_details where ad_id=‘1110330’ ORDER BY modify_date desc
Query 2#SELECT * FROM ad_details WHERE LOGIN_ID=‘cccmann’ ORDER BY modify_date desc
Here is table schema for fields being used in query:
±-----------------±---------------±-----±----±------------------±----------------------------+
| Field | Type | Null | Key | Default | Extra |
±-----------------±---------------±-----±----±------------------±----------------------------+
| AD_ID | varchar(50) | NO | PRI | NULL | |
LOGIN_ID | varchar(100) | YES | MUL | NULL | |
| MODIFY_DATE | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
…
…
all columns are indexed, ad_id is the primary key.
if I do explain of both the queries i could see Query No 2 is using filesort respective of index on modify_date ( in order by clause )
mysql> explain SELECT * FROM ad_details WHERE LOGIN_ID=‘cccmann’ ORDER BY MODIFY_DATE DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ad_details
type: ref
possible_keys: login_id_idx
key: login_id_idx
key_len: 103
ref: const
rows: 1
Extra: Using where; Using filesort
mysql> explain SELECT * FROM ad_details where ad_id=‘1110330’ ORDER BY modify_date desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ad_details
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 52
ref: const
rows: 1
Extra:
why is explain output are different?
why Query 1 is using index rather than filesort?