Re: simple uses of order by datetime keep causing 'using filesort'

[Rick James]

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.
Analysis:
* 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.
Analysis:
* 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.