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?
Answer
It's not because it can't use the index. It's because the optimizer thinks it's faster not to use the index and do a filesort1. You should see different behaviour in MyiSAM and InnoDB tables.
InnoDB creates the PRIMARY key as a clustered one (or the first UNIQUE if no primary is defined) and this can be used for queries that have ORDER BY pk or WHERE pk BETWEEN low AND high because all the values needed are in this clustered key and in consecutive locations (the clustered key is the table).
MyISAM tables have only B-tree indices so if the query used this index, it would have to read that entire index and it would have the comment_id values in the wanted order (that's really good) but it would then have to read the table as well (not so good) to get all the other wanted columns. So, the optimizer thinks that since it's going to read the table, why not scan it all and do the filesort? You can test that by trying:
SELECT comment_id FROM comments ORDER BY comment_id ;
It will use the index and do no filesort because the query needs only the values that are stored in the index.
If you want a similar (to InnoDB) behaviour in MyiSAM, you coud try creating an index on (comment_id, comment_content, comment_author, comment_author_url) and then try your query. All the needed values would be found on the index and in correct order so no filesort would be performed.
The additional index will need of course almost as much space on disk as the table.
1: filesort is not always bad and it does not mean that a file is saved on disk. If the size of the data is small, it is performed in memory.