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 usuário 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 Secçã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:

E aqui estão alguns exeplos: