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

GROUP BY TERMO_PRODUTO.ID_PRODUTO

HAVING COUNT(*)=3)

Eliminação das stopwords

Ao criar a tabela TERMO é possível, com o objetivo de otimizar as buscas, eliminar as chamadas stopwords  (artigos, preposições etc.) que se repetem muito.

 

Desempenho

 

Em um teste siumulado com dados gerados aleatóriamente (500 mil produtos com cerca de 100 mil termos diferentes e 2,5 milhões de ocorrências dos termos) em um notebook  dual core de 1,9 MZ o tempo para uma pesquisa com 3 termos e imperceptível enquanto que usando usando a varredura com o operador LIKE levou 8 segundos

Conclusões

 

Quando as facilidades intrínsecas de full text search não estão disponíveis, pode-se, com uma modelagem simples e um pequeno esforço adicional na inclusão/alteração dos campos textuais, obter, rapidamente, os resultados de uma pesquisa full text, em uma única consulta SQL.

Scripts do exemplo

 

Scripts gerados no SQl Server 2000:

 

CREATE TABLE [dbo].[TERMO_PRODUTO] (

                [ID_TP] [int] NOT NULL ,

                [ID_TERMO] [int] NOT NULL ,

                [ID_PRODUTO] [int] NOT NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[PRODUTO] (

                [ID_PRODUTO] [int] IDENTITY (1, 1) NOT NULL ,

                [DESCRICAO] [nvarchar] (100) COLLATE Latin1_General_CI_AS NOT NULL

) ON [PRIMARY]

GO

 

CREATE TABLE [dbo].[TERMO] (

                [ID_TERMO] [int] NOT NULL ,

                [TERMO] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL

) ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[TERMO_PRODUTO] ADD

                CONSTRAINT [aaaaaTERMO_PRODUTO1_PK] PRIMARY KEY  NONCLUSTERED

                (

                               [ID_TP]

                )  ON [PRIMARY]

GO

 

 CREATE  INDEX [ix_ID_PRODUTO] ON [dbo].[TERMO_PRODUTO]([ID_PRODUTO]) ON [PRIMARY]

GO

 

 CREATE  INDEX [ix_ID_TERMO] ON [dbo].[TERMO_PRODUTO]([ID_TERMO]) ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[PRODUTO] ADD

                CONSTRAINT [aaaaaPRODUTO1_PK] PRIMARY KEY  NONCLUSTERED

                (

                               [ID_PRODUTO]

                )  ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[TERMO] ADD

                CONSTRAINT [aaaaaTERMO1_PK] PRIMARY KEY  NONCLUSTERED

                (

                               [ID_TERMO]

                )  ON [PRIMARY]

GO

 

 CREATE  INDEX [ix_TERMO] ON [dbo].[TERMO]([TERMO]) ON [PRIMARY]

GO

 

ALTER TABLE [dbo].[TERMO_PRODUTO] ADD

                CONSTRAINT [TERMO_PRODUTO_FK00] FOREIGN KEY

                (

                               [ID_TERMO]

                ) REFERENCES [dbo].[TERMO] (

                               [ID_TERMO]

                )

GO