MySQL Forums
Forum List  »  Optimizer & Parser

Re: simple uses of order by datetime keep causing 'using filesort'
Posted by: Rick James
Date: August 20, 2014 07:34PM

That is probably by design. Let me walk through the approach the optimizer took and the approach you advocate.

Plan A: Filesort:
* Ignore the index.
* Do a "table scan" into a temp table (possible MEMORY, possibly MyISAM).
* Sort the temp table.
* Output the rows coming from the sort.
* Doing the table scan is a sequential read of all the data.
* Creating the temp table is either a write to MEMORY (fast) or a sequential write to a MyISAM table (not bad).
* The Filesort is very efficient.

Plan B: Use the 'perfect' index.
* Scan through the entire index, which is in the desired order.
* For each index entry, do a _random_ lookup into the data. (Note: The secondary index has a copy of the PRIMARY KEY; this is then used to fetch the desired row from the data.)
* Output the rows thus found.
* The "random lookup" is _assumed_ to be a performance killer. For huge tables, it certainly is. For tiny tables, you probably cannot tell whether it matters. For 100K-row tables, it probably depends on innodb_buffer_pool_size, disk speed, caching, etc, etc.

Run an experiment...
* Time the SELECT the way you have it. (Do this twice to avoid cache issues.)
* Run it with a FORCE INDEX(..). (Twice). Also EXPLAIN it to see if it uses Plan B.

Please report your timings.

There _will_ be cases where the optimizer "gets it wrong". But these are not very often.

Options: ReplyQuote

Written By
Re: simple uses of order by datetime keep causing 'using filesort'
August 20, 2014 07:34PM

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.