Search code examples
mysqlindexingdatabase-performanceexplain

Mysql Order by clause using "FileSort"

I have a table structure like

comment_id primary key
comment_content 
comment_author
comment_author_url

When I fire query like 

explain SELECT * FROM comments  ORDER BY comment_id

It outputs the results as

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  comments    ALL     NULL    NULL    NULL    NULL    22563   Using filesort

Why is not able to find the index that I have defined as Primary Key?

Solution

  • Anytime a sort can’t be performed from an index, it’s a filesort.

    The strange thing here is that you should have the index on that field since it is a primary key(and a primary key column is implicitly indexed), testing on a test database i just noticed that MySQL use FileSort anytime you perform a SELECT *, this is a no sense behaviour (i know) but if you rewrite your query in this way : 

    SELECT comment_id, comment_content, comment_author, comment_author_url 
FROM comments  
ORDER BY comment_id

    it will use the index correctly . Maybe could be a bug of mysql ...