Optimización de consultas MySQL LIKE '%string%' en InnoDB

Muchas aplicaciones requieren buscar una cadena dentro de un cuerpo de texto. MySQL ofrece un operador LIKE para realizar este tipo de búsquedas, pero ¿cómo podemos optimizar estas consultas para obtener un rendimiento óptimo?

Problema

Considere la siguiente tabla:

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

Al ejecutar una consulta como:

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

podemos esperar que la base de datos Utilice un índice para acelerar la búsqueda. Sin embargo, si agregamos un índice simple en la columna de palabras clave usando:

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

una consulta EXPLAIN revela que MySQL aún necesita escanear toda la tabla.

Solución

MySQL usa índices haciendo coincidir el inicio de la cadena de búsqueda. Para consultas como LIKE 'lo que sea%', el índice se puede utilizar porque 'lo que sea' está anclado al inicio de la cadena.

Sin embargo, consultas como LIKE '%lo que sea%' no tienen ese ancla. El término de búsqueda aparece "flotando" dentro de la cadena, lo que obliga a MySQL a escanear todo el campo.

Para optimizar dichas consultas, podemos usar índices de texto completo. Estos índices están diseñados específicamente para búsquedas "flotantes". InnoDB admite índices de texto completo desde la versión 5.6.4, lo que lo convierte en una opción viable para optimizar consultas LIKE '%string%'.