MySQL Forums
Forum List  »  Optimizer & Parser

Re: always uses filesort even after adding indexes
Posted by: Rick James
Date: August 22, 2012 10:52PM

Not "always".

Please provide
SHOW CREATE TABLE
SHOW TABLE STATUS
Then we can discuss why it happens for that case.
Provide some more cases, if you like.

"Using index" means that all the fields in the SELECT were found in a single INDEX.

"Using filesort" means that did not fetch the rows in the order needed for the GROUP BY or ORDER BY. Even with a suitable index, it may decide to read the table, then sort it. Why?...

Using an index means bouncing back and forth between the index (a BTree in one place) and the data (in another place). This random jumping around is sometimes perceived as being slower than doing a "filesort".

A "filesort" does not necessarily mean creating a disk file and sorting it; it sometimes refers to an in-memory sort.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: always uses filesort even after adding indexes
1255
August 22, 2012 10:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.