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