On lun, 2003-02-03 at 16:59, Jan Hidders wrote:
Anyway, there is actually a "trick" that you
can use here to get always the
chronological order and that is to define an in index on (old_namespace,
old_title, old_timestamp) and forcing MySQL to use that index
Okay, finally got around to trying this (on test wiki). The EXPLAIN
results are looking very promising -- it gets rid of the "using
filesort" and is presumably sorting with the index... *but only if
sorting forwards*.
If it were just the order the results come in, no big deal, but we need
to grab the last X rows and I don't know of another way to do that than
a reverse sort and LIMIT. No one wants to transfer all 15,000 of mav's
edits from the db just to display the last 50! (Well, I suppose you
could do two queries, one just to count, then one forward-sorted with
LIMIT N-X,X. But, that's ugly and increases load on the db.)
MySQL 3.x doesn't support using an index to sort backwards. However, if
I'm reading the docs correctly*, MySQL 4.x _does_. This may be a strong
argument in favor of upgrading.
*
http://www.mysql.com/doc/en/ORDER_BY_optimisation.html
-- brion vibber (brion @
pobox.com)