MySQL - searching only indexed portion of a TEXT

resources

#1

So I have a column which is set as content TEXT with an index of KEY content (content(255)). Now, since a TEXT can be up to 65k+ characters, I obviously don’t want to search its entire data. Is there a way to do a WHERE selecting only the indexed portion of the text, i.e. the first 255 characters?


#2

It is possible to use SUBSTR() to get only the first X characters, but if you’re trying to use that in a WHERE clause then it’s still not going to be very efficient. The MySQL query analyzer will use the index to restrict the rows but it won’t know to use the index for a text match so it will always compare against the full column as far as I know.

I would suggest instead adding another column (like content_255) and duplicating the first 255 characters from your content column. Index that column and query against it. Using another 255 characters for another column is a small tradeoff given the performance improvement you’ll get. You could use a stored procedure to set its value whenever content is updated so you can avoid potentially getting out of sync.