mysql: reversed content col for LIKE '%foo' - good practise?

Question

I was recently watching a google lecture on mysql and learnt that MySQL wont use indexes for LIKE '%foo' searches.

My site's 'live search' (ajax search) searches the column full_details(TEXT), should i create a reversed version like full_details_rev(TEXT) and index that?

Or should i not index large text fields at all?

Answer 1

Maybe an external fulltext indexing tool like Sphinx is more appropriate in this case.

Answer 2

For fulltext searching (which is basically what you're doing), the fulltext index is much more efficient - for a brief intro, see e.g. this article; Google for more.

Example of use (assuming there is a fulltext index, otherwise you'll just get an error):

SELECT some_column FROM yourtable WHERE MATCH (full_details) AGAINST ('foo')

(a normal index on a reversed column would help you iff the original ends exactly with the search string (i.e. 'asdfsdafsdfaaaaafoo')