optimize - order by mysql php

Optimizar MySQL LIKE consultas ''% string%'' en innoDB

Question

Tener esta mesa:

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

Nos gustaría optimizar la siguiente consulta:

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

La tabla es InnoDB, (por lo que no FULLTEXT por el momento), ¿cuál sería el mejor índice para usar con el fin de optimizar dicha consulta?

Hemos intentado un simple:

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

Pero una consulta de explicación muestra que es necesario escanear toda la tabla si nuestras consultas son LIKE ''whatever%'' en su lugar, este índice funciona bien, pero de lo contrario no tiene ningún valor.

¿Hay alguna forma de optimizar esto para innoDB?

¡Gracias!

Answer 1

Los índices se crean desde el inicio de la cadena hacia el final. Cuando utilizas la cláusula de tipo LIKE ''whatever%'' , MySQL puede usar esos índices basados ​​en el inicio para buscar rápidamente.

Pero cambiando a LIKE ''%whatever%'' elimina ese anclaje al comienzo de la cadena. Ahora, los índices basados ​​en el inicio no se pueden usar, porque el término de búsqueda ya no está anclado al comienzo de la cadena; está "flotando" en algún lugar en el medio y todo el campo debe buscarse. Cualquier consulta LIKE ''%... nunca puede usar índices.

Es por eso que utilizas índices de texto completo si todo lo que estás haciendo son búsquedas ''flotantes'', porque están diseñadas para ese tipo de uso.

Nota importante: InnoDB ahora admite índices de texto completo a partir de la versión 5.6.4. A menos que no pueda actualizar al menos a 5.6.4, no hay nada que le impida usar InnoDB * AND búsquedas de texto completo.

Answer 2

Me gustaría comentar que sorprendentemente, la creación de un índice también ayudó a acelerar las consultas para consultas like ''%abc%'' en mi caso.

Ejecutando MySQL 5.5.50 en Ubuntu (dejando todo en forma predeterminada), he creado una tabla con muchas columnas e insertado 100,000 entradas ficticias. En una columna, inserté cadenas completamente aleatorias con 32 caracteres (es decir, todas son únicas).

Ejecuté algunas consultas y luego agregué un índice en esta columna. Un simple

select id, searchcolumn from table_x where searchcolumn like ''%ABC%''

devuelve un resultado en ~2 seconds sin el índice y en 0.05 seconds con el índice.

Esto no se ajusta a las explicaciones anteriores (y en muchas otras publicaciones). Cuál podría ser la razón para eso?

EDITAR He verificado la salida EXPLAIN. El resultado dice que las filas son 100,000 , pero la información adicional es " Using where; Using index ". Entonces, de alguna manera, ¿el DBMS tiene que buscar todas las filas, pero todavía puede utilizar el índice?