Artigos Banco de Dados Índices FULLTEXT no MySQL

Atenção: por essa edição ser muito antiga não há arquivo PDF para download.
Os artigos dessa edição estão disponíveis somente através do formato HTML.

Clique aqui para ler todos os artigos desta edição

O MySQL, a partir da versão 3.25.25, provê o mecanismo de índices fulltext, efetuando buscas textuais com maior precisão. Este recurso é mais poderoso que o uso de like, pois, além de ordenar o resultado pela similaridade semântica, oferece mais opções para filtragem na consulta.

Aplicações com grande massa de texto que precisam efetuar pesquisas baseadas na relevância são candidatas ao uso de índices fulltext. O exemplo mais comum são páginas de busca, que retornam os resultados mais relevantes na frente. Podemos destacar também bibliotecas virtuais, pesquisas em arquivos de registro ou pesquisas em documentos que estão armazenados no banco de dados.

Criação do índice fulltext

Na versão 4.1 do MySQL, os tipos de dados suportados são VARCHAR, TEXT e CHAR. Este índice pode ser criado através do comando ALTER TABLE, CREATE TABLE ou CREATE INDEX. Vejamos alguns exemplos:

ALTER TABLE websites ADD FULLTEXT(titulo, descricao);

O comando acima cria um índice fulltext composto dos campos titulo e descricao, na tabela websites. Dessa forma, a consulta feita sobre esse índice levará em consideração a ocorrência do texto pesquisado nos dois campos.

CREATE FULLTEXT INDEX INDEX_FT_DESCRICAO ON websites (titulo,descricao) ;

Esta linha cria um índice fulltext chamado INDEX_FT_DESCRICAO para os campos titulo e descricao.

CREATE TABLE websites(
          post_id mediumint(8) unsigned NOT NULL,
          titulo varchar(100) NOT NULL,
          descricao text, 
          PRIMARY KEY (post_id),
             FULLTEXT (titulo, descricao));

O comando acima implementa o mesmo índice fulltext na tabela websites.

Evite criar índices fulltext em tabelas que sofrerão alguma rotina de importação, pois a carga de registros com esse índice é mais lenta. O ideal é criar o índice depois que a importação for concluída.

Para efetuar a pesquisa através de um índice fulltext utilizamos as funções MATCH e AGAINST, que recebem o nome dos campos e o valor a ser pesquisado, respectivamente. Veja o exemplo:

SELECT titulo, descricao
 FROM websites
 WHERE MATCH (titulo, descricao) AGAINST ('SQL Magazine');

NOTA: É obrigatório passar todos os campos do índice como parâmetro de MATCH

Como funciona a pesquisa?

Para cada registro, o MySQL atribui um valor de relevância, que representa a similaridade da string de pesquisa com a linha em questão. Um valor de relevância 0 (zero) significa nenhuma semelhança, fazendo com que o registro não seja exibido. O cálculo de relevância é feito através de um algoritmo projetado para pesquisa em grandes massas de texto, tornando a busca inadequada para pequenas tabelas. Entre as variáveis que são levadas em consideração nesse cálculo, o MySQL considera o número de palavras encontradas em cada campo do índice, o número de palavras encontradas por linha, o número de ocorrências da mesma palavra em todas as linhas, entre outros. Quanto mais rara for a palavra, maior será seu peso no cálculo da relevância.

Para exemplificar, usaremos a tabela de websites, cuja estrutura foi definida no tópico anterior. Execute as instruções abaixo para popular a tabela:

INSERT INTO websites VALUES (1,'SQL Magazine - Feita para desenvolvedores','Revista especializada em tecnologia, fala sobre o universo dos bancos de dados de forma altamente técnica');
 INSERT INTO websites VALUES (2,'Java Magazine - Revista da comunidade JAVA brasileira','Lançada em 2002, a Java Magazine foi a primeira revista na américa latina a tratar exclusivamente sobre Java');
 INSERT INTO websites VALUES (3,'ClubeDelphi - Tudo sobre Delphi, Kylix e InterBase’,’Nas bancas de todo Brasil, a ClubeDelphi traz todos mês os melhores artigos para o programador Delphi');
 INSERT INTO websites VALUES (4,'Borland Brasil','Atua na implantação de soluções de alta performance para e-business');
 INSERT INTO websites VALUES (5,'Microsoft Brasil','Catálogo de produtos, novidades e dicas');
 INSERT INTO websites VALUES (6,'Microsoft Network Brasil','Notícias, downloads, bate-papo,jogos online e mais');
 INSERT INTO websites VALUES (7,'Oracle Brasil','Atua nos segmentos: banco de dados, comércio eletrônico, desenvolvimento, apoio à decisão, gestão empresarial, serviços.');

Em seguida, execute o comando SELECT abaixo:

SELECT post_id, titulo, descricao
 FROM websites
 WHERE MATCH (titulo, descricao) AGAINST ('Java Magazine');

O resultado obtido será o seguinte:

2 Java Magazine - A revista... Lançada em 2002, a Java Magazine...

1 SQL Magazine - Feita para... Revista especializada em tecnologia...

Para exibir o valor de relevância atribuído para cada linha, podemos inserir o comando MATCH na lista de campos:


SELECT titulo, MATCH (titulo,descricao) AGAINST ('Java Magazine') 
 FROM websites
 WHERE MATCH (titulo, descricao) AGAINST ('Java Magazine');

Resultado:

Java Magazine - A revista... 4.1781345660722

SQL Magazine - Feita para... 0.78148462038476

Repare que a função MATCH( ) foi utilizada duas vezes. Não se preocupe, pois o otimizador de consultas percebe que as funções são idênticas e a chamada ocorre apenas uma vez. Observe mais alguns exemplos:

SELECT titulo, MATCH (titulo,descricao) AGAINST ('banco de dados') 
 FROM websites
 WHERE MATCH (titulo, descricao) AGAINST ('banco de dados');

Oracle Brasil 2.3325152825749

SQL Magazine - Feita para... 0.78148462038476 

SELECT titulo, MATCH (titulo,descricao) AGAINST ('microsoft') , descricao
 FROM websites
 WHERE MATCH (titulo, descricao) AGAINST ('microsoft');

Microsoft Brasil 0.85714756726439

Microsoft Network Brasil 0.82178538281238

MATCH vs LIKE

Diversos aspectos diferenciam o mecanismo de MATCH do uso de LIKE. Vejamos:

  • O comando MATCH é mais veloz, tendo em vista a indexação de cada palavra do campo que faz parte do índice fulltext.
  • A pesquisa fulltext foi criada com o objetivo de fornecer uma busca semântica em bases que contenham muito texto. Dessa forma, o MySQL desconsidera palavras com menos de quatro caracteres. Expressões como “de”, “que” e “ou” são excluídas automaticamente da pesquisa. Esta restrição é justificável na maioria dos casos, dada a baixa seletividade destas palavras em pesquisas textuais. Veja o exemplo:

SELECT titulo, descricao
 FROM websites
 WHERE MATCH (titulo, descricao) AGAINST ('SQL');
  
 (0 row(s) returned)

  • Uma palavra presente em mais de 50% dos registros será excluída da pesquisa, pois o MySQL considera sua relevância baixa. Exemplo:

SELECT titulo, descricao
 FROM websites
 WHERE MATCH (titulo, descricao) AGAINST ('Brasil');
  
 (0 row(s) returned)

O MySQL possui uma lista de palavras desconsideradas na pesquisa, conhecidas como stopwords. A lista contém termos comuns em inglês, como "able", "about", "above", "according", entre outros. Só é possível personalizar as stopwords alterando o arquivo ft_static.c, disponível no diretório myisam, nos fontes do MySQL. Após alterar o arquivo, devemos recompilar o MySQL e reconstruir os índices fulltext. Na lista de solicitações para as novas versões do MySQL está a possibilidade de configurar as stopwords de uma forma mais simples.

Executando pesquisas fulltext em modo booleano

A partir da versão 4.0.1, o MySQL disponibiliza o recurso de pesquisa fulltext com parâmetros booleanos, aumentando significativamente o poder na construção de filtragens de texto.

A pesquisa booleana tem como base a manipulação de strings de acordo com alguns operadores. Veja a lista dos operadores disponíveis:

+ : a string deve estar presente em todos os registros retornados;

- : a string não deve estar presente nos registros retornados;

*: trabalha com parte da palavra a ser procurada;

“ ”: retorna a string entre aspas duplas exatamente da maneira como foi digitada;

( ): Agrupa palavras em sub-expressões;

< >: Muda a contribuição da string no cálculo da relevância. O operador < decrementa a relevância e o operador > aumenta a relevância;

~ : age como operador de negação. A contribuição de relevância da string se torna negativa.

NOTA: A pesquisa booleana desconsidera o filtro de 4 letras mínimas e de 50% de ocorrência no resultado. Portanto, se você precisa de uma busca que não leve em consideração essas restrições, utilize o modo booleano.

Veja alguns exemplos:

SELECT titulo, descricao
 FROM websites
 WHERE MATCH (titulo, descricao) 
 AGAINST ('+Brasil -Microsoft' IN BOOLEAN MODE);

Retorna os registros que não possuem a string “Microsoft” e que possuem obrigatoriamente a string “Brasil”. Confira o resultado:

ClubeDelphi - Tudo sobre ... Nas bancas de todo Brasil...

Borland Brasil Atua na implantação ...

Oracle Brasil Atua nos segmentos: banco de dados...

Observe que o MySQL não levou em consideração a restrição de 50% de ocorrência para efetuar a pesquisa.

SELECT titulo, descricao
 FROM websites
 WHERE MATCH (titulo, descricao) 
 AGAINST ('Mag*' IN BOOLEAN MODE);

Retorna os registros que possuem qualquer palavra iniciando com a string “Mag”. Resultado:

SQL Magazine - Feita para ... Revista especializada em tecnologia...

Java Magazine - Revista da ... Lançada em 2002, a Java Magazine...

SELECT titulo, descricao
 FROM websites
 WHERE MATCH (titulo, descricao) 
 AGAINST ('"Microsoft Brasil"' IN BOOLEAN MODE);

Retorna os registros que possuem a ocorrência exata da string “Microsoft Brasil“. Observe que o site “Microsoft Network Brasil” não é retornado:

Microsoft Brasil Catálogo de produtos...

SELECT titulo, 
 MATCH (titulo, descricao) AGAINST ('Oracle IN BOOLEAN MODE)
 FROM websites
 WHERE MATCH (titulo, descricao) 
 AGAINST ('Oracle IN BOOLEAN MODE);

Neste comando o peso da palavra business é reduzido no cálculo de relevância:

Borland Brasil 0.66666668653488

Oracle Brasil 1

Se o operador “<” não tivesse sido utilizado, teríamos o seguinte resultado

Borland Brasil 1

Oracle Brasil 1

O operador “>” produz o efeito inverso, confira:

SELECT titulo, MATCH (titulo, descricao) 
 AGAINST ('>Oracle business' IN BOOLEAN MODE)
 FROM websites
 WHERE MATCH (titulo, descricao) 
 AGAINST ('>Oracle business' IN BOOLEAN MODE);

Borland Brasil 1

Oracle Brasil 1.5

SELECT titulo, MATCH (descricao,titulo) 
 AGAINST ('+Brasil +(Network)' IN BOOLEAN MODE)
 FROM websites
 WHERE MATCH (descricao,titulo) 
 AGAINST ('+Brasil +(Network)' IN BOOLEAN MODE)

No comando acima vemos o uso de parênteses. Neste caso, a combinação “Brasil Microsoft” tem um peso menor do que a combinação “Brasil Network”. Veja o resultado:

Microsoft Brasil 0.83333337306976

Microsoft Network Brasil 1.3333333730698

SELECT titulo, 
 MATCH (titulo,descricao) AGAINST ('dados ~oracle' IN BOOLEAN MODE)
 FROM websites       
 WHERE MATCH (titulo,descricao) AGAINST ('dados ~oracle' IN BOOLEAN MODE);

O uso de “~” faz com que a palavra perca a importância na pesquisa, sem efetivamente excluir a linha que a contém, como faz o operador “-“. Dessa forma, utilize “~” para diminuir o valor de relevância da frase que contém a palavra em questão. Veja o resultado:

SQL Magazine - Feita para ... 1

Oracle Brasil 0.5

Sem o operador de negação o resultado seria:

SQL Magazine - Feita para ... 1

Oracle Brasil 2

Resumo das características da pesquisa fulltext:

  • Desconsidera palavras com menos de quatro caracteres, caso IN BOOLEAN MODE não seja utilizado;
  • Desconsidera palavras presentes em mais de 50% dos registros, caso IN BOOLEAN MODE não seja utilizado;
  • É indicada para tabelas textuais grandes, contendo centenas ou milhares de registros;
  • Palavras hifenizadas são tratadas em separado. Por exemplo, em azul-celeste, o mecanismo busca pela ocorrência de azul e/ou celeste;
  • Por default, os registros são retornados por ordem descendente de relevância;
  • Palavras da lista de stopwords são desconsideradas;
  • A pesquisa é case-insensitive (por exemplo, não há diferença entre “Server” ou “server”);
  • Uso de várias opções de operadores booleanos;
  • Melhor performance que o operador like;
  • A pesquisa em modo booleano também pode funcionar sem a existência de um índice fulltext. A performance, no entanto, será menor.

Algumas limitações do mecanismo de pesquisa:

  • Um dos recursos solicitados pelos usuários do MySQL é o aumento no poder de parametrização de pesquisas fulltext.
  • A criação deste tipo de índice causa uma queda no desempenho de operações INSERT e UPDATE. A perda é maior se comparada com o uso de índices comuns sobre colunas texto e mais explícita quando temos tabelas muito grandes.
  • O parâmetro utilizado em AGAINST( ) deve ser do tipo string;
  • Este recurso possui uma grande lista to-do na documentação do MySQL, com itens que deverão ser implementados nas próximas versões do banco. Entre eles, podemos destacar o uso de fulltext em tabelas MERGE, melhorias no algoritmo com o intuito de produzir pesquisas ainda mais rápidas, regionalização da lista de stopwords e mais flexibilidade para o administrador configurar o comportamento da pesquisa.
Conclusão

O bom uso do índice fulltext depende de um fator principal: o tamanho da tabela a ser pesquisada. Se a tabela for pequena, resultados absurdos podem ser gerados, visto que o algoritmo foi criado para tabelas com muitos registros.

Como todo índice, fulltext também degrada a performance nas operações de atualização. Cabe ao administrador do banco avaliar as vantagens obtidas neste tipo de pesquisa, não deixando de levar em consideração o tipo de aplicação que está sendo desenvolvida.

Mais informações:

http://www.mysql.com/documentation/index.html