Richard Holton wrote:
A question for you MySQL gurus out there:
Currently, the categorylinks table uses the index
cl_sortkey(cl_to,cl_sortkey(128)),
Note that the cl_sortkey field in the table is varchar(255).
In the categoryPage query, there is an ORDER BY clause on cl_sortkey.
However, this query ends up using a filesort. I've found that if the
index is changed to use the full width of the cl_sortkey field, then
the ORDER BY does not need the filesort.
Does anyone know how to eliminate the need for a filesort without
having to expand the index to the full width of cl_sortkey? Or should
we just bite the bullet and use the full width?
I've tried ORDER BY LEFT(cl_sortkey,128), but that doesn't help
(probably no big surprise).
-Rich Holton
Rich,
I don't know the situation in detail, but this looks like there's an
index on the sortkey, which will be used by default if you sort on the
entire thing. Sort on a substring, or any other transformation of the
sortkey, and the database will not be able to use its pre-existing
index. Why would you want to sort on a subkey, anyway, if sorting on the
full key is more efficient?
(Incidentally, I'm having fun with databases at work, too. I generate
complex SQL queries programmatically from logic expressions, only to
find that the DB will tend to generate implicit outer joins rather than
doing even the simplest evaluation of constant-expressions. So I end up
having to do everything myself, leading the DB slowly through series of
SELECTs linked up by bits of trivial sorting-and-optimization code. DBs
are dumber than rocks.)
-- Neil