Utilizando o MySQL FullText Index Search

Hoje vamos falar sobre um recurso muito útil no MySQL, o Full Text Index e Search.

Um recurso muito poderoso que hoje nas versões 5.5 esta disponível apenas para a Engine MyISAM mas como podemos ver na documentação do FullText no site do MySQL, nas versoes 5.6 estará também disponível para InnoDB.

Normalmente quando se quer procurar uma palavra ou expressão em uma coluna usamos o LIKE CORINGA EXPRESSAO CORINGA (LIKE ‘%EXPRESSAO%’) caso estejamos procurando por 2 palavras ficaria LIKE ‘%PALAVRA1%PALAVRA2%) o que muita gente não sabe e que este processo eh custoso para o mysql e não performatico, para estes casos podemos usar o FullText index.

O comando eh simples MATCH() … AGAINST (), onde MATCH especificamos a(s) coluna(s) que desejamos procurar, sim, podemos utilizar o full text para procurar em mais de uma coluna, desde que todas as colunas dentro do MATCH esteja especificadas no índice e AGAINST e onde especificamos a(s) palavra(s) que queremos procurar, podemos especificar também o modo de procura, que vou falar mais tarde neste artigo.

Vamos criar a nossa tabela e colocar a mão na massa:

CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`body` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM;
INSERT INTO `articles` VALUES (1,'MySQL Tutorial','DBMS stands for DataBase ...'),(2,'How To Use MySQL Well','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. ...'),(5,'MySQL vs. YourSQL','In the following database comparison ...'),(6,'MySQL Security','When configured properly, MySQL ...');

Vamos fazer nossa primeira consulta, procurando por artigos que abordem ‘database’

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.01 sec)

Vamos agora procurar por ‘database tutorial’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('database tutorial');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
3 rows in set (0.01 sec)

Agora vamos procurar por artigos que abordem o tema ‘MySQL’, detalhe, todos os artigos em nossa tabela contem a palavra MySQL

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('MySQL');

Empty set (0.00 sec)

Por que? Vamos então falar sobre modos de busca, por padrão, o MySQL utiliza o modo de busca ‘Natural Language’ que nos diz que se a palavra procurada estiver presente em 50% ou mais linhas a consulta não vai bater.

Outra função interessante e que o MySQL nos permite trocar o modo de busca, vamos então repetir a consulta em modo Booleano:

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('MySQL' IN BOOLEAN MODE);

+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | 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. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

6 rows in set (0.00 sec)

Ok, mas o que muda entre um e outro? pra que serve o modo booleano? Como o nome já diz, true or false, vamos fazer uma busca por ‘database’ mas eu não quero que sejam exibidas linhas que contenham a palavra tutorial, complicou? não, veja como:

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('+database -tutorial' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
1 row in set (0.01 sec)

Por hoje e só, no próximo artigo, vamos abordar mais a fundo o modo Booleano, alterando a ordem de relevância

Gostou ? Ajude e Compartilhe!
Esta entrada foi publicada em index, MySQL, performance. Adicione o link permanente aos seus favoritos.
  • Parabéns pelo artigo sobre FullText Marcelo, mandou muito bem!! Vamo que vamo, happy mysql’ing!

    • marceloaltmann

      Valeu!!!

  • Cristiano

    Muito bom o artigo, costumo usar o LIKE e não conhecia o full text. Continue escrevendo 🙂

  • Wazirpur

    Is there any solution for InoDB database engine type because I have heard FULLTEXT Index is available only in myISLAM dtabase engine types… Any hint..

    • marceloaltmann

      Yes, it’s available from MySQL 5.6, you can have a look on the online Docs

  • Nehru Place

    I want to search few words from a paragraph which is stored in the mysql text field, so can it be done using full text index. By the way it is informative content you have provided here.. thanks for that !

  • Leandro

    Procurando mais referencias sobre o assunto eu encontrei uma forma interessante de se manipular as relevancias:
    SELECT articles.*,
    MATCH (title, body) AGAINST (‘MySQL’) AS relevance,
    MATCH (title) AGAINST (‘MySQL’) AS title_relevance
    FROM articles
    WHERE MATCH (title,body) AGAINST (‘MySQL’)
    ORDER BY title_relevance DESC, relevance DESC

    — alternativa:
    ORDER BY title_relevance + relevance DESC

    De qualquer forma o artigo me interessou e espero que o exemplo acima incremente ao texto =P

    Valeu, abs

  • Leandro

    Excelente artigo, ajudou muito, vou testar esse script do Leandro acima também que parece bem interessante!

    Abraços

  • deepshikha

    Nice tutorial, helped me a lot. Thanks 🙂

  • Marcos

    estou usando a pesquisa como esta acima em meu sistema, me deparei com o seguinte problema quando pesquisa fio som, ele pesquisaria fio e depois som, depois de ralar muito cheguei a seguinte conclusão ele não faz pesquisa com 3 caracteres se pesquisa qualquer coisa com mais de 4 ou mais caracteres ele vai isso em meu banco de dados

    SELECT pro.cd_produto, pro.ref_produto, pro.nm_produto, pro.nm_produto as pes, pro.unidade, pro.vl_produto, pro.estoque From pro_produto As pro Where match(pro.nm_produto) against (‘FIO SOM’ IN BOOLEAN MODE) Order By pes, pro.nm_produto Asc

    tem logica o que estou falando ou to falando besteira

    • marceloaltmann

      Olá Marcos,
      Tem muita lógica o que falou.
      Isso ocorre pois por padrão, o MySQL “desabilata” a consulta o FULL TEXT INDEX se as palavra for menor que 4 carácteres, esse comportamento pode ser alterado configurando a variável ft_min_word_len.

      Podes olhar a documentação para mais detalhes.

      Abs.

  • Hugosw

    olá, estou tentando utilizar o full-text mas não estou conseguindo achar por parte de palavras. Por exemplo, tenho cadastrado “Motorola” e estou tentando pesquisar por “Motoro” e não está trazendo nenhum registro. Existe alguma outra função para esse tipo de pesquisa?

    • Marcelo Altmann

      Consegue mandar a estrutura da tabela(show create table) e a query que está rodando?

      • Hugosw

        Já achei uma solução, adicionei um asterisco no final da palavra pesquisada assim a consulta trouxe os registros esperados.

  • Lauro Daniel Glassmann

    Muito obrigado, salvou o coração do meu sistema pois ele não estava encontrando algumas buscas e em seu artigo vi que é poque estão presentes em mais de 50% dos resultados, alterei e ficou perfeito, muito obrigado1

  • mark stevens

    Hi, I want to search only on `title` column like this:
    select * from articles where match(title) against(‘tutorial’)
    But I always get error: ` Can’t find FULLTEXT index matching the column list`. So how can I only search on a column?

    • Marcelo Altmann

      Hi Mark,

      Fulltext require that all collumns from the index and the match criteria to be equal (same collumns, same order), if you want to search only on title, run this:

      ALTER TABLE articles ADD FULLTEXT(title);

      • mark stevens

        Yeah, I tried it and understand the thing, thanks.

  • Fala Marcelo, beleza?

    Leal seu exemplo. Gostaria de saber se consigo trazer sometne parte de um texto que está indexado… por exemplo, tenho um livro inteiro indexado (content) e procuro uma palavra que está em seu conteúdo (content), precisava trazer uma pequena parte que contenha a palavra procurada… conseguimos fazer isso com o fulltext ?

    Obrigado!

    • Marcelo Altmann

      Fala Evert.
      Na verdade o full text vai lhe ajudar a identificar a linha inteira que contem o string que está procurando, pra exibir, tens que usar funções de string (tanto no MySQL quanto na tua linguagem de programação), um exemplo de como poderia fazer pra mostrar parte do texto que contenha a palavra procurada:


      mysql> SELECT SUBSTRING(body, LOCATE('database', body)-10) from articles WHERE MATCH(title,body) AGAINST ('database');
      +----------------------------------------------+
      | SUBSTRING(body, LOCATE('database', body)-10) |
      +----------------------------------------------+
      | following database comparison ... |
      | tands for DataBase ... |
      +----------------------------------------------+
      2 rows in set (0.00 sec)

      Mais informações sobre funções para strings podem ser encontradas aqui http://dev.mysql.com/doc/refman/5.7/en/string-functions.html

      Espero ter ajudado.

      • Ajudou demais!! no caso como tenho um livro, tive de utilizar assim:

        SELECT SUBSTRING(body, LOCATE('database', body)-50,100) from articles WHERE MATCH(title,body) AGAINST ('database');

        • Marcelo.. porém no modelo acima somente traz a primeira ocorrência do texto. Precisava de todas as ocorrências da palavra. Tentei com o Solr, mas estou com dificuldade de trazer o trecho também… (highlight).

          Obrigado!

          • Marcelo Altmann

            Evert, sugiro utilizar alguma linguagem de programação para extrair todas as ocorrências, no PHP por exemplo, pode pegar o conteudo todo, e rodar um explode pela string que está procurando, ele vai te retornar um array e tu pode percorrer este array para mostrar todas as ocorrências.

            Abs

          • Valeu!

            Tentarei desta forma.

            Abs,

  • Marcos

    Parabéns pelo ótimo artigo.
    Procurei na documentação e não achei se tem como aplicar stemming. Você já utilizou stemming no MySQL? Caso afirmativo, como?

    Obrigado

    • Marcelo Altmann

      Fala Marcos.

      Já tentou utilizar o ‘*’ ?
      Exemplo da documentação:
      ‘apple*’ – vai retornar “apple”, “apples”, “applesauce”, or “applet”.

      • Marcos

        Obrigado pela ideia, vou fazer alguns testes.

        • Marcos

          Obrigado Marcelo. Tive que aplicar um Stemmer externo para extrair o radical e depois usar o operador *.

  • Curtis Newton

    in boolean mode ? oh please dont give any explanation

  • Pedro

    Excelente artigo!!!

    Como ficaria a consulta:
    select * from articles WHERE title like ‘%tutorial’;

    Utilizando o MATCH/AGAINST ?

  • Alexson Scheppa Peisino

    Excelente artigo. Me ajudou bastante.

    VI vários tutoriais ensinando usar o full text. tudo uma complicação só!