Full text search otimizada em SQL usando GROUP BY

Resumo

Alguns Bancos de Dados já disponibilizam facilidades de pesquisa  em texto integral (full text) , como é o caso do MySQL( a partir da versão 4). Em outros as pesquisas textuais são feitas através de  módulos adicionais separados ou de produtos de terceiros.

Na ausência dessas facilidades, muitas vezes os programadores recorrem ao uso do operador LIKE para localizar palavras em campos textuais, o que exige varreduras em toda a tabela.

O artigo mostra como fazer pesquisas em campos textuais otimizadas usando apenas instruções de SQL e que pode ser usado, praticamente, em qualquer banco de dados.

Exemplo simples

O processo será explicado através de um exemplo simples que consiste em pesquisar produtos através de palavras que constem da sua descrição.

Teríamos a Tabela PRODUTO com os seguintes campos:

• ID_PRODUTO: Chave sequencial;
• DESCRICAO: texto com a descrição do produto;

Demais campos: preço, peso etc.

Modelo de dados para a pesquisa

Para permitir a pesquisa duas tabelas auxiliares precisam ser criadas e atualizadas na inclusão/alteração dos produtos:

Tabela TERMO :

• ID_TERMO: chave sequencial;
• TERMO: termo ou palavra do texto, sem duplicações.

Tabela TERMO_PRODUTO

• ID Chave sequencial;
• ID_TERMO: chave estrangeira do termo que ocorre na descrição de um produto, sem duplicações em cada produto;
• ID_PRODUTO: Chave estrangeira do produto.

Diagrama E-R

Para essas tabelas devem ser criados índices adequados para garantir a integridade e acelerar as pesquisas.

Exemplos das tabelas

Exemplo de pesquisa

Como é usual, a pesquisa é feita através de n (uma ou mais) palavras com as opções: Todas as Palavras (operador e), Qualquer palavra (operador ou) e Frase exata.

Suponhamos que foram fornecidos os termos: monitor LCD 19 com a opção Todas as palavras.

Consultas em SQL

Opção 1: Todas as palavras

Para obter a lista das chaves dos produtos que atendem a pesquisa basta fazer uma única consulta em SQL:

SELECT TERMO_PRODUTO.ID_PRODUTO

FROM TERMO INNER JOIN TERMO_PRODUTO ON TERMO.ID_TERMO = TERMO_PRODUTO.ID_TERMO

WHERE

TERMO.TERMO=''monitor'' OR

TERMO.TERMO=''LCD''

OR TERMO.TERMO=''19''

GROUP BY TERMO_PRODUTO.ID_PRODUTO

HAVING COUNT(*)=3

Opção 2: Qualquer palavra

Para a opção Qualquer palavra deve ser eliminada a cláusula HAVING COUNT(*)=3

Opção 3: Frase exata

Essa opção exige uma varredura nos produtos encontrados na consulta da opção 1,  como subconsulta:

SELECT PRODUTO.ID_PRODUTO FROM PRODUTO

 WHERE DESCRICAO LIKE ''%monitor LCD 19 %''  AND PRODUTO.ID_PRODUTO IN (

SELECT  TERMO_PRODUTO.ID_PRODUTO

FROM TERMO INNER JOIN TERMO_PRODUTO

ON TERMO.ID_TERMO = TERMO_PRODUTO.ID_TERMO

WHERE

TERMO.TERMO=''monitor'' OR

TERMO.TERMO=''LCD''

OR TERMO.TERMO=''19''