¿Cómo acelerar SELECT .. LIKE consultas en MySQL en múltiples columnas?

Tengo una tabla MySQL para la que hago frecuentes SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%' consultas. ¿Algún tipo de índice ayudaría a acelerar las cosas?

Hay unos pocos millones de registros en la tabla. Si hay algo que acelere la búsqueda, ¿afectaría seriamente el uso del disco por los archivos de la base de datos y la velocidad de las INSERT y DELETE ? (no se realiza ninguna UPDATE alguna vez)

Actualización : rápidamente después de la publicación, he visto mucha información y discusión sobre la forma en que se usa LIKE en la consulta; Me gustaría señalar que la solución debe usar LIKE '%text%' (es decir, el texto que estoy buscando se antepone y se agrega con un% comodín). La base de datos también tiene que ser local, por muchas razones, incluida la seguridad.

Un índice no aceleraría la consulta, porque para columnas textuales los índices funcionan indexando N caracteres comenzando desde la izquierda. Cuando le GUSTA ‘% text%’ no puede usar el índice porque puede haber un número variable de caracteres antes del texto.

Lo que deberías hacer es no utilizar una consulta como esa en absoluto. En su lugar, debe usar algo como FTS (Búsqueda de texto completo) que MySQL admite para tablas MyISAM. También es muy fácil crear dicho sistema de indexación para las tablas que no son MyISAM, solo necesita una tabla de índice separada donde almacene palabras y sus ID relevantes en la tabla actual.

Un índice no ayudará a la correspondencia de texto con un comodín principal, un índice se puede usar para:

 LIKE 'text%' 

Pero supongo que eso no servirá para nada. Para este tipo de consulta, debería estar buscando un proveedor de búsqueda de texto completo si desea escalar la cantidad de registros que puede buscar. Mi proveedor preferido es Sphinx , muy completo / rápido, etc. Lucene también merece una visita. Un índice de texto completo en una tabla MyISAM también funcionará, pero en última instancia buscar MyISAM para cualquier base de datos que tenga una cantidad significativa de escrituras no es una buena idea.

Un índice no se puede usar para acelerar las consultas donde los criterios de búsqueda comienzan con un comodín:

LIKE '%text%'

Un índice puede (y podría ser, según la selectividad) utilizado para los términos de búsqueda del formulario:

LIKE 'text%'

Añadiría que en algunos casos puede acelerar la consulta usando un índice junto con like / rlike si el campo que está mirando a menudo está vacío o contiene algo constante.

En ese caso, parece que puede limitar las filas que se visitan utilizando el índice agregando una cláusula “y” con el valor fijo.

Intenté esto para buscar ‘tags’ en una tabla enorme que generalmente no contiene muchas tags.

SELECT * FROM objects WHERE tags RLIKE("((^|,)tag(,|$))" AND tags!=''

Si tiene un índice en las tags, verá que se usa para limitar las filas que se están buscando.

Tal vez puedas intentar actualizar mysql5.1 a mysql5.7.

Tengo alrededor de 70,000 registros. Y ejecute el siguiente SQL:

 select * from comics where name like '%test%'; 

Lleva 2000 ms en mysql5.1. Y lleva 200 ms en mysql5.7 o mysql5.6.

Otra alternativa para evitar exploraciones de tablas completas es seleccionar subcadenas y verificarlas en la statement que tenga:

 SELECT al3.article_number, SUBSTR(al3.article_number, 2, 3) AS art_nr_substr, SUBSTR(al3.article_number, 1, 3) AS art_nr_substr2, al1.* FROM t1 al1 INNER JOIN t2 al2 ON al2.t1_id = al1.id INNER JOIN t3 al3 ON al3.id = al2.t3_id WHERE al1.created_at > '2018-05-29' HAVING (art_nr_substr = "FLA" OR art_nr_substr = 'VKV' OR art_nr_subst2 = 'PBR');