Optimizing MySQL LIKE '%string%' Queries in InnoDB

Many applications require searching for a string within a body of text. MySQL offers a LIKE operator to perform such searches, but how can we optimize these queries for optimal performance?

Problem

Consider the following table:

CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

When executing a query like:

SELECT id FROM example WHERE keywords LIKE '%whatever%'

we may expect the database to use an index to speed up the search. However, if we add a simple index on the keywords column using:

ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);

an EXPLAIN query reveals that MySQL still needs to scan the entire table.

Solution

MySQL uses indexes by matching the start of the search string. For queries like LIKE 'whatever%', the index can be used because 'whatever' is anchored at the start of the string.

However, queries like LIKE '%whatever%' have no such anchor. The search term appears "floating" within the string, forcing MySQL to scan the entire field.

To optimize such queries, we can use fulltext indexes. These indexes are specifically designed for "floating" searches. InnoDB has supported fulltext indexes since version 5.6.4, making it a viable option for optimizing LIKE '%string%' queries.