�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

�ndices FULLTEXT no MySQL

Artigo da Revista SQL Magazine -Edição 3.

Criaç�o do �ndice fulltext

Como funciona a pesquisa?

MATCH vs LIKE

Executando pesquisas fulltext em modo booleano

Artigos relacionados