Faster alternative to LIKE %term% ?

[jkurrle]

Is there a faster alternative to using LIKE %term% in MySQL? I am looking at having to do a text search within a varchar field for a specific term, but when using %term%, I found out it will disable the index on the field.

I was reading up on REGEXP, but from what I've seen in user comments, it can be anywhere from 20% to 40% (or even more!) slower than using LIKE %term%.

I was looking at setting a FULLTEXT index against the field, then using MATCH/AGAINST, but a FULLTEXT index only works on MyISAM tables, not InnoDB. I'm not sure if I want to be locked into a MyISAM table, especially if there are going to be a lot of updates...

Does anyone have any suggestions as to what might be a faster solution?

[Res]

Wildcard searches are dangerous and slow 🙁

I'd say best option is handle in the same way forums do. Create a second table that is myisam and stores the text as a fulltext index. You can then search against this table and join it on your parent table. You could continue to store the text in both places and set a trigger up to update the fulltext table on update, but personally I'd just leave it as a reference.