MySQL Forums
Forum List  »  Performance

Re: Multi-column index when part 1 is a TEXT column
Posted by: Nicolas Turgeon
Date: March 04, 2005 04:07PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Multi-column index when part 1 is a TEXT column
1961
March 04, 2005 04:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.