XOOPS Brasil

 

Pesquisa Full-text no MySQL

MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION] )

A partir da versão 3.23.23, MySQL tem suporte para indexação e busca full-text. Índices full-text no MySQL são um índice do tipo FULLTEXT. Índices FULLTEXT são usados apenas com tabelas MyISAM e podem ser criadas a partir de colunas CHAR, VARCHAR ou TEXT durante um CREATE TABLE ou adicionados posteriormente com ALTER TABLE ou CREATE INDEX. Para banco de dados maiores, será muito mais rápido carregar seus dados em uma tabela que não tnha índices FULLTEXT, que criar o índice com ALTER TABLE (ou CREATE INDEX). Carregar dados em uma tabela que já tenha um índice FULLTEXT será muito mais lento.

Pesquisa full-text é realizada com a função MATCH().

mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO articles VALUES
-> (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
-> (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
-> (NULL,'Optimizing MySQL','In this tutorial we will show ...'),
-> (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
-> (NULL,'MySQL Security', 'When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

A função MATCH() realiza um busca de linguagem natural por uma string contra uma coleção de texto (um conjunto de uma ou mais colunas incluídas em um índice FULLTEXT). A string pesquisada é dada como o argumento de AGAINST(). A busca é realizada na forma caso-insensitivo. Para cada uma das linhas da tabela, MATCH() retorna um valor relevante, isto é, uma medida de similaridade entre a string pesquisada e o texto naquela nas colunas identificadas na lista MATCH().

Quando MATCH() é utilizado na cláusula WHERE (veja exemplo acima) as linhas retornadas são automaticamente ordenadas com a maior relevância primerio. Valores de relevância são números de ponto flutuante não negativos. Relevância zero significa nenhuma similaridade. Relevância é computado baseada no número de palavras na linha, o número de palavras única naquela linha, o número de palavras na coleção e o número de documentos (linhas) que contenham uma palavra particular.

Também é possível realizar uma busca no modo booleano. Isto é explicado posteriormente nesta seção.

O exemplo precedente é uma ilustrção básica mostrando como usar a função MATCH(). Linhas são retornodas em ordem decrescente de relevância.

O próximo exemplo mostra como retornar o valores de relevância explicitamente. Como nem a cláusula WHERE nem a ORDER BY estão presentes, as linhas são retornadas fora de ordem.

mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
| 1 | 0.64840710366884 |
| 2 | 0 |
| 3 | 0.66266459031789 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

O exemplo seguinte é mais complexo. A consulta retorna a relevância e ainda ordena as linhas em ordem decrescente de relevância. Para conseguir este resultado, você deve especificar MATCH() duas vezes. Isto não irá causar sobrecarga adicional, pois o otimizador MySQL irá notar que duas chamadas MATCH() são idênticas e invocam o código da busca full-text apenas uma vez.

mysql> SELECT id, body, MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root') AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body | score |
+----+-------------------------------------+-----------------+
| 4 | 1. Never run mysqld as root. 2. ... | 1.5055546709332 |
| 6 | When configured properly, MySQL ... | 1.31140957288 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

Desde a versão 4.1.1, pesquisas full-text suportam expansão de consulta (em particular, sua variante ``blind query expansion''). Ela é geralmente útil quando uma frase pesquisada é muito curta, o que normalmente significa que um utilizador está confiando em um conhecimento contido, que a pesquisa full-text normalmente perde. Por exemplo, um usuario pesquisanado por ``database'' podem realmente significar que ``MySQL'', ``Oracle'', ``DB2'', ``RDBMS'' são todas frases que devem coincidir com ``databases'' e devem ser encontrados também. Isto é conhecimento contido. Blind query expansion (also known as automatic relevance feedback) works by performing the search twice, where the search phrase for the second search is the original search phrase concatenated with the few top found documents from the first search. Thus, if one of these documents contained the word ``databases'' and the word ``MySQL'', then the second search will find the documents that contain the word ``MySQL'' but not ``database''. Another example could be searching for books by Georges Simenon about Maigret, when a user is not sure how to spell ``Maigret''. Then, searching for ``Megre and the reluctant witnesses'' will find only ``Maigret and the Reluctant Witnesses'' without query expansion, but all books with the word ``Maigret'' on the second pass of a search with query expansion. Note: because blind query expansion tends to increase noise significantly, by returning non-relevant documents, it's only meaningful to use when a search phrase is rather short.

O MySQL utiliza um analizados muito simples para separar texto em palavras. Uma ``palavra'' é uma sequência de caracteres consistindo de letras, digitos, ‘'’, e ‘_’. Qualquer ``palavra'' presente na lista de palavra de parada ou for muito curta é ignorada. O tamanho padrão mínimo das palavras que serão encontradas pela pesquisa full-text é de quatro caracteres. Isto pode ser alterado como descrito em Seção 6.8.2, “Ajuste Fino de Pesquisas Full-text no MySQL”.

Toda palavra correta na lista de coleções e na consulta é pesada de acordo com sua significância na consulta ou coleção. Deste modo, uma palavra que está presente em vários documentos terá peso menor (e poderá ter até mesmo um peso zero), já que ele têm um valor semântico baixo nesta coleção particular. Por outro lado, se a palavra é rara, ela receberá um peso alto. O peso das palavras são então combinados para computar a relevância das linhas.

Tal técnica funciona melhor com coleções grandes (de fato, ela é cuidadosamente ajustado deste modo). Para tabelas muito pequenas, a distribuição das palavras não refletem adequadamente seus valores semânticos, e este modelo pode algumas vezes produzir resultados bizarros.

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

A busca pela palavra MySQL não produz resultados no exemplo acima, porque esta palavra está presente em mais da metade das linhas. Como tal, ela é efetivamente tratada como palavra de parada (isto é, uma palavra com valor semântico zero). Este é o comportamento mais desejável --- uma consulta de linguagem natural não deve retornar toda segunda linha de uma tabela de 1 GB.

Uma palavra que casa com metade dos registros em uma tabela tem menos chance de encontrar dosumentos relevantes. De fato, é muito mais provável encontrar vários documentos irrelevantes. Todos nós sabemos que isto acontece com muita frequência quando tentamos encontrar alguma coisa na internet com um mecanismo de busca. É com esta razão que estes registros tem sido atribuído com um baixo valor semântico neste banco de dados particular.

Na versão 4.0.1, MySQL também pode realizar buscas full-text booleanas usando o modificador IN BOOLEAN MODE.

mysql> SELECT * FROM articles WHERE MATCH (title,body)
-> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+------------------------------+-------------------------------------+
| id | title | body |
+----+------------------------------+-------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 2 | How To Use MySQL Efficiently | After you went through a ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
+----+------------------------------+-------------------------------------+

Esta consulta recupera todos os registros que contenham a palavra MySQL (note: o ponto inicial de 50% não é utilizado), mas que não contenha a palavra YourSQL. Note que a pesquisa em modo booleano não ordena os registros automaticamente em ordem decrescente de relevância. Você pode ver isto no resultado da consulta anterior, onde a linha com a maior relevância (aquela que contém MySQL duas vezes) é listada por último, não em primeiro. Um busca full-text booleana também pode funcionar mesmo sem um índice FULLTEXT, no entanto ela seria lenta.

A busca full-text booleana suporte potencialmente as seguintes operações:

  • +

    Um sinal de mais precedente indica que esta palavra deve estar presente em cada linha retornada.

  • -

    Um sinal de menos precedente indice que esta palavra não deve estar presente em qualquer linha retornada.

  • Por padrão (quando nem mais nem menos é especificado) a palavra é opcional, mas as linhas que a contém serão avaliadas positivamente. Isto define o comportamento de MATCH() ... AGAINST() sem o modificados IN BOOLEAN MODE.

  • < >

    Estes dois operadores são usados para alterar a contribuição de uma palvara no valor de relevância que á tribuído a um registro. O operador < reduz a contribuição e o operador > a aumenta. Veja o exemplo abaixo.

  • ( )

    Parenteses são usado para agrupar palavras em subexpressões.

  • ~

    Um til precedente atua como um operador de negação, tornando a contribuição da palavra para a relevância da linha ser negativa. Ele é útil para marcar palavras "ruidosas". Linhas com tais palavras terão uma avaliação mais baixa que outras, mas não será excluída, como seria com o operador -.

  • *

    Um asterisco é um operador de truncamento. Diferente dos outros operadores, ele deve ser inserida ao fim da palavra, não deve ser precedente.

  • "

    A frase que é colocada entre aspas duplas ", coincidem apenas com linhas que contenha esta frase literalmente, como foi digitada.

E aqui estão alguns exeplos:

  • apple banana

    encontra linhas que contenha pela menos uma destas palavras.

  • +apple +juice

    ... ambas as palavras.

  • +apple macintosh

    ... palavra ``apple'', mas avaliada mais alto se também conter ``macintosh''.

  • +apple -macintosh

    ... palavra ``apple'' mas não ``macintosh''.

  • +apple +(>turnover <strudel)

    ... ``apple'' e ``turnover'', ou ``apple'' e ``strudel'' (em qualquer ordem), mas avalia ``apple pie'' melhor que ``apple strudel''.

  • apple*

    ... ``apple'', ``apples'', ``applesauce'', e ``applet''.

  • "some words"

    ... ``some words of wisdom'', mas não ``some noise words''.

6.8.1. Restrições Full-text

  • Pesquisas full-text são suportadas apenas por tabelas MyISAM.

  • Pesquisas full-text pode ser usadas com UCS-2 (mas funcionam com UTF-8 a partir do MySQL 4.1.1).

  • Todos os parâmetros da função MATCH() devem ser colunas da mesma tabela que é parte do mesmo índice FULLTEXT, a menos que MATCH() esteja IN BOOLEAN MODE.

  • Todas as colunas no índice FULLTEXT devem ter o mesmo conjunto de caracter.

  • A lista de coluna MATCH() deve casar exatamente a lista de colunas em algum definição de índice FULLTEXT para a tabela, a menos que este MATCH() seja IN BOOLEAN MODE.

  • O argumento para AGAINST() deve ser uma string constante.

6.8.2. Ajuste Fino de Pesquisas Full-text no MySQL

Infelizmente, pesquisas full-text ainda possui poucos parâmetros de ajuste, embora adicionar alguns seja de grande prioridade no TODO. Se você tiver uma distribuição fonte do MySQL (see Seção 2.3, “Instalando uma distribuição com fontes do MySQL”), você pode exercer maior controle sobre o comportamenteo de pesquisas full-text.

Note que o busca full-text foi cuidadosamente ajustada para a melhor busca efetiva. Mofificar o comportamento padrão irá, na maioria dos casos, apenas tornar os resultados de busca piores. Não alteren o fonte do MySQL a menos que você saiba o que está fazendo!

A descrição das variáveis full-text na lista a seguir devem ser configuradas no servidor na inicialização. Você não pode modificá-los dinamicamente enquanto o servidor estiver em execução.

  • O tamanho mínimo de palavras a serem indexadas é definido pela variavel ft_min_word_len do MySQL. Veja mais informações sobre isto na Seção 4.6.8.4, “SHOW VARIABLES.

    (Esta variável só está disponível a partir do MySQL versão 4.0.)

    O valor padrão é quatro caracteres. Altere-o para o valor de sua preferência e reconstrua os seus índices FULLTEXT. Por exemplo, se você quiser pesquisar palavras de três caracteres, você pode definir esta variável colocando a seguinte linha no arquivo de opções:

    [mysqld]
    ft_min_word_len=3
    

    Então reinicie o servidor e reconstrua seus índices FULLTEXT.

  • A lista de palavras de parada pode ser carregada do arquivo especificado pela variável ft_stopword_file. Veja mais informações sobre isto na Seção 4.6.8.4, “SHOW VARIABLES. Reconstrua o seu índice FULLTEXT depois de modificar a lista de palavras de parada. (Esta varável só está disponível a partir do MySQL versão 4.0.10 e posterior)

  • O ponto inical de 50% é determinado pelo esquema de pesagem particular escolhido. Para disabilitá-lo, altere a seguinte linha em myisam/ftdefs.h:

    #define GWS_IN_USE GWS_PROB
    

    Para:

    #define GWS_IN_USE GWS_FREQ
    

    Então recompile o MySQL. Não há necessidade de reconstruir o índice neste caso. Note: fazendo isto você diminui em muito a habilidade do MySQL fornecer valores de relevância adequados para a função MATCH(). Se você realmente precisa buscar por tais palavras comuns, seria melhor fazê-lo utilizando IN BOOLEAN MODE, que não observa o poonto inicial de 50%.

  • Algumas vezes o mantedor do mecanismo de busca gostaria de alterar os operadores usados por busca full-text boolanas. Eles são definidos pela variável ft_boolean_syntax. Veja mais informações sobre isto na Seção 4.6.8.4, “SHOW VARIABLES. Ainda, esta variável é somente leitura; este valor está definido em myisam/ft_static.c.

Para mudanças full-text que exigem que você reconstrua seu índice FULLTEXT, o modo mais fácil de fazê-lo para uma tabela MyISAM é usar a seguinte instrução, a qual reconstroi o arquivo de índice:

mysql> REPAIR TABLE nome_tabela QUICK;

6.8.3. TODO de Pesquisas Full-text

  • Fazer todas as operações com índices FULLTEXT mais rápidas.

  • Operadores de proximidade

  • Supporte para "always-index words". Elas poderiam ser quaisquer strings que o utilizador quisesse tratar como palavra, os exemplos são "C++", "AS/400", "TCP/IP", etc.

  • Suporte a busca full-text em tabelas MERGE.

  • Suporte a UCS-2.

  • Tornar a lista de palavras de parada dependente da linguagem dos dados.

  • Stemming (dependente da linguagem dos dados. é claro).

  • Pre-analizadores de UDF genéricas fornecidas pelo utilizador.

  • Tornar os modelos mais flexíveis (adicionando algum parâmetro ajsutável a FULLTEXT em CREATE/ALTER TABLE).