Re: Multi-column index when part 1 is a TEXT column
Hi Harrison,
Thanks for your prompt reply. Yes, I knew that we could not use ORDER BY (without filesort) on an index that is a prefix of the whole column width.
In my example however, I'm doing ORDER BY on the *second* index part (b in my example), which *not* a prefix. Only the first part (a) has a prefix. Yet, if column 'a' is a VARCHAR(50), the filesort is not necessary for ORDER BY b. What I don't understand is why there is no filesort if column 'a' is a VARCHAR(50), and there is if 'a' is TEXT. In both cases, the index on column 'a' is a prefix (20), and column 'b' doesn't change at all.
As a side note, in case of a collision (different 'a' values with the same first 20 characters), I assume, it would have to fetch information for too many elements (all those with the same first 20 characters), but they should still be ordered properly by 'b' using the part-2 index, no?
Thanks,
-Nicolas