create an index on a mysql column based on the length its contents?

Question

How do I create an index on a column in mysql v 5.0 (myisam db engine) based upon the length of its value its a TEXT data type up to 7000 characters, do I have to add another column with the length of the first column.

Any help kindly received, cheers

Answer 1

Sounds like a good approach to me (sorry don't know mysql, but in oracle you could set a trigger so that when your main column is updated the "length" column gets automatically updated)

Answer 2

Yes, as MySQL doesn't support function-based indexes (like ADD INDEX myIndex(LENGTH(text))), you'll need a new int column and define a trigger to auto-update it after inserts and updates.