Optimizing MySQL LIKE ‘%string%’ queries in innoDB

Indexes are built from the start of the string towards the end. When you use LIKE 'whatever%' type clause, MySQL can use those start-based indexes to look for whatever very quickly.

But switching to LIKE '%whatever%' removes that anchor at the start of the string. Now the start-based indexes can’t be used, because your search term is no longer anchored at the start of the string – it’s “floating” somewhere in the middle and the entire field has to be search. Any LIKE '%... query can never use indexes.

That’s why you use fulltext indexes if all you’re doing are ‘floating’ searches, because they’re designed for that type of usage.

Of major note: InnoDB now supports fulltext indexes as of version 5.6.4. So unless you can’t upgrade to at least 5.6.4, there’s nothing holding you back from using InnoDB *AND fulltext searches.

Leave a Comment

tech