Como acelerar o SELECT .. LIKE consultas no MySQL em várias colunas?

Eu tenho uma tabela MySQL para a qual eu faço muito freqüentes SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%' consultas. Algum tipo de índice ajudaria a acelerar as coisas?

Existem alguns milhões de registros na tabela. Se houver algo que acelere a pesquisa, isso afetaria seriamente o uso do disco pelos arquivos do database e a velocidade das INSERT e DELETE ? (nenhuma UPDATE é executada)

Atualização : Rapidamente após postar, tenho visto muita informação e discussão sobre a maneira como o LIKE é usado na consulta; Gostaria de salientar que a solução deve usar LIKE '%text%' (ou seja, o texto que estou procurando é prefixado e anexado com um caractere curinga%). O database também deve ser local, por vários motivos, incluindo segurança.

Um índice não agilizaria a consulta, pois, para índices de colunas textuais, o trabalho indexa N caracteres começando da esquerda. Quando você faz LIKE ‘% text%’ ele não pode usar o índice porque pode haver um número variável de caracteres antes do texto.

O que você deve fazer é não usar uma consulta como essa. Em vez disso, você deve usar algo como o FTS (Full Text Search) que o MySQL suporta para tabelas MyISAM. Também é muito fácil criar esse sistema de indexação para tabelas não-MyISAM, você só precisa de uma tabela de índice separada onde você armazena palavras e seus IDs relevantes na tabela real.

Um índice não ajuda a correspondência de texto com um curinga principal, um índice pode ser usado para:

 LIKE 'text%' 

Mas eu estou supondo que não vai cortar. Para esse tipo de consulta, você realmente deve procurar um provedor de pesquisa de texto completo, se quiser dimensionar a quantidade de registros que pode pesquisar. Meu provedor preferido é Sphinx , muito cheio de resources / rápido etc. Lucene também pode valer a pena dar uma olhada. Um índice de texto completo em uma tabela MyISAM também funcionará, mas, no final das contas, buscar MyISAM para qualquer database que tenha uma quantidade significativa de gravações não é uma boa idéia.

Um índice não pode ser usado para acelerar as consultas em que os critérios de pesquisa começam com um caractere curinga:

LIKE '%text%'

Um índice pode (e pode ser, dependendo da seletividade) usado para termos de pesquisa do formulário:

LIKE 'text%'

Gostaria de acrescentar que, em alguns casos, você pode acelerar a consulta usando um índice junto com like / rlike se o campo que você está olhando estiver sempre vazio ou contiver algo constante.

Nesse caso, parece que você pode limitar as linhas que são visitadas usando o índice, adicionando uma cláusula “e” com o valor fixo.

Eu tentei isso para procurar ‘tags’ em uma tabela enorme que normalmente não contém muitas tags.

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

Se você tiver um índice nas tags, verá que ele é usado para limitar as linhas que estão sendo pesquisadas.

Talvez você possa tentar atualizar o mysql5.1 para o mysql5.7.

Eu tenho cerca de 70.000 registros. E corra seguindo o SQL:

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

Demora 2000ms no mysql5.1. E demora 200ms no mysql5.7 ou no mysql5.6.

Outra alternativa para evitar varreduras de tabelas completas é selecionar substrings e verificá-las na instrução having:

 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');