I’ve been doing some experiments with some relatively large MySQL tables with trying to get around using a filesort when selecting rows ordered by a datetime in descending order. I’m not sure how much real world benefit this has, but some of the initial results are encouraging. We’ll see.
Since MySQL doesn’t allow you to define a reverse index (which would be useful for a field that you’re constantly sorting by descending order), I’m experimenting with an indexed column that’s set with 2^32 - UNIX_TIMESTAMP(time), where time’s a normal MySQL timestamp. This way, you can ORDER BY countdown without the DESC keyword. I’m still experimenting with different indexes and queries, but there seem to be at least some areas where it leads to improved performance by avoiding the file sort. If more results seem encouraging enough, I’ll write up the comparisons.