Re: Multi-column index when part 1 is a TEXT column
Hi,
As you saw, when you use an index on a prefix of an index it can't possibly know the data is in proper ORDER. The reason is that there could be possible duplicates within the first 20 characters.
Imagine I had the following data in a table:
alice
alex
bob
And I created an index on (col(1)). Internally they all get indexed by only the first character. Due to the fact it is only the first character, there is no guarentee that the data is in proper order, so MySQL will have to do a filesort to make sure.
There is no way to get what you want to do currently. In MySQL 5.0 the varchar field can be up to 64k and you can create an index on those fields up to a certain size (like 1000 or 2000), which might be able to help you. Generally creating really big indexes such as that isn't that good though as they take up a lot of space and add lots of overhead, so it should only be done on a specific case by case scenario.
Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com
Subject
Views
Written By
Posted
2693
March 04, 2005 09:31AM
Re: Multi-column index when part 1 is a TEXT column
1875
March 04, 2005 02:41PM
2042
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.