Otimizando consultas LIKE '%string%' do MySQL no InnoDB

Muitos aplicativos exigem a pesquisa de uma string dentro de um corpo de texto. O MySQL oferece um operador LIKE para realizar essas pesquisas, mas como podemos otimizar essas consultas para um desempenho ideal?

Problema

Considere a seguinte tabela:

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

Ao executar uma consulta como:

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

podemos esperar que o banco de dados use um índice para acelerar a pesquisa. No entanto, se adicionarmos um índice simples na coluna de palavras-chave usando:

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

uma consulta EXPLAIN revela que O MySQL ainda precisa verificar a tabela inteira.

Solução

O MySQL usa índices combinando o início da string de pesquisa. Para consultas como LIKE 'whatever%', o índice pode ser usado porque 'whatever' está ancorado no início da string.

No entanto, consultas como LIKE '%whatever%' não possuem tal âncora. O termo de pesquisa aparece "flutuando" dentro da string, forçando o MySQL a varrer todo o campo.

Para otimizar tais consultas, podemos usar índices de texto completo. Esses índices são projetados especificamente para pesquisas “flutuantes”. InnoDB suporta índices de texto completo desde a versão 5.6.4, tornando-o uma opção viável para otimizar consultas LIKE '%string%'.