MySQL Forums
Forum List  »  Performance

Re: Multi-column index when part 1 is a TEXT column
Posted by: Harrison Fisk
Date: March 04, 2005 02:41PM


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:


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

Options: ReplyQuote

Written By
Re: Multi-column index when part 1 is a TEXT column
March 04, 2005 02:41PM

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.