Having this table:
CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
SELECT id FROM example WHERE keywords LIKE '%whatever%'
ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);
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.