GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

explain : different output for a query using order by

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • explain : different output for a query using order by

    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?






  • #2
    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 directly to the page with all the row data so it might doesn't need to do sorting. Even in query 1, type: const while in query 2, type: ref so are you using unique index for login_id or it's normal? Is there any possibilities for duplicate values in login_id?

    Comment


    • #3
      login_id isnt unique index, its normal and there are chances of NULL values as well.

      Comment

      Working...
      X