Want ORDER BY to use indexed portion of long text field

[Bob Fitterman]

I am trying to design a table that has (1) long text columns and (2) won't incur a filesort when using ORDER BY on one of those columns. Displays returned to the user only need to be sorted on the first 40 characters of the column, even if that is not unique.

Is there some syntax I can use in the ORDER BY clause that will let the server know it's okay to use the existing index to sort the data? I tried things like ORDER BY LEFT(txt, 40) and ORDER BY CAST(txt as CHAR(40)) but those don't avoid the filesort. I have tried this with both VARCHAR(10000) and TEXT types.

The only workaround I have come up with so far is to introduce a separate column that repeats the first 40 characters of the data in another column, but I think that is asking for a real easy way to corrupt the results when some programmer forgets to be sure that both fields are always updated simultaneously.



Edited 1 time(s). Last edit at 10/13/2007 07:04AM by Bob Fitterman.