Not the answer you need?
Register and ask your own question!

explain : different output for a query using order by

yogesh777yogesh777 ContributorCurrent User Role Beginner
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?

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    ad_id is primary key (cluster index) while login_id is secondary index. so when you are searching record through primary key, it leads
  • yogesh777yogesh777 Contributor Current User Role Beginner
    login_id isnt unique index, its normal and there are chances of NULL values as well.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.