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.
comments powered by Disqus
The term "filesort" in the MySQL EXPLAIN output is kind of misleading... it doesn't actually mean that any sorting was done by file. It basically just means that a quicksort was performed, for a small data set this will be done in memory. Because the MyISAM and InnoDB engines can quickly retrieve from either side of the index, the desc sort is really not much slower for a well written query.
So the order by time desc should still be very fast... but of course I'd be interested to see if you could contradict me with results that say otherwise. :-)
The real killer is not in sorting indexed values, but in sorting unindexed values. Learning how to create compound indexes that let your frequent queries sort well is awesome... and also one place where your faked countdown index idea could be very useful.
I haven't gotten back to it for a bit, and results haven't seemed to be entirely consistent yet. At the moment, it looks like something to file under "promising, but probably won't have real world benefit". The place it would actually be most useful might be comments, which can already be sorted appropriately usually by primary key so it's less of a big deal. We'll see what I can get though.
I'm at the redesigning tables stage right now, so I'm doing random experiments with indexing, arranging, etc. I imagine a lot of it will just get thrown out.
I was told awhile back by a MySQL person that it wouldn't make any difference. Alas, but it was worth trying out at least.