Mysql Order by clause using "FileSort"

Question

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 1

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 filesort¹. 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.

---

¹: 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.