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