Busca em tabela mysql com 8 milhões de registros

Programação
#1

Olá pessoal,

Tenho um site de busca em php pesquisando em uma tabela do mysql com 8 milhões de registros. Está tabela possui o (campo titulo e o campo descrição), ambos campos com 8 milhões de registros e ambos campos com índice FULLTEXT.

A minha busca utiliza as funções (MATCH e AGAINST) IN BOOLEAN MODE e com (LIMIT 10) para retornar sempre os 10 primeiros registros encontrados, porem quando a minha tabela tinha apenas uns 3 milhões de registros a busca era rápida, mas agora com 8 milhões de registros na tabela, tem certas buscas que está muito lenta levando mais de 1 minuto para retornar o que foi pesquisado.

Acredito que utilizar índice invertido (lista invertida) não resolva o meu problema, pois há algum tempo astras em outro projeto eu já tive uma lista invertida no MYSQL para uma tabela com apenas 600 mil registros e a lista invertida ficou com uns 5 milhões de TERMOS e terminou que a busca sequencial na tabela com os 500 mil registros erá mais rápida que percorrer a lista invertida com milhares de termos para encontrar algo. Agora imagina fazer uma lista invertida para uma tabela com 8 milhões de registros, a minha lista invertida teria centenas de milhares de TERMOS o que seria mais lento pesquisar na lista que na própria tabela de forma sequencial com índice fulltext.

Alguém poderia me dizer uma solução para ter performance pesquisando em uma tabela mysql com 8 milhões de registros?

Abraço

#2

Oi @jeanpjm,

Nao tenho uma resposta exata para seu problema, mas algumas propostas de como eu atacaria esse problema:

  • Primeiro, existe outros campos nessa tabela que você possa aplicar um filtro mais eficiente, antes da busca fulltext no título e descriçao?

  • Já verificou o plano de execuçao da query e alternativas que o próprio Mysql oferece com relaçao a isso?

  • O uso de um indíce invertido é muito eficiente e escala bem para esse tipo de pesquisa. É provável que para tabelas maiores os resultados sejam melhores do que para tabelas menores (onde uma busca sequencial em poucos registros ainda é mais rápida que o overhead do indíce invertido)

  • É viável utilizar uma tecnologia dedicada a esse tipo de busca? Com Elasticsearch, por exemplo, vejo queries em índices com mais de 100 milhoes de documentos sendo executados em menos de 1 segundo (claro, com um cluster de tamanho adequado, mas aí é só adicionar mais máquinas no cluster).

Edit:

  • Há sempre a possibilidade de escalar verticalmente o servidor Mysql também, se houver condiçoes.
#3

Pra esse cenário invistam no Oracle, se bem administrado o full text search vai funcionar bem com milhões de registros.