Using the Mysql FullText Index Search

Send to Kindle

Today let’s talk about a resource very useful on MySQL, the FullText Index and Search
This resource is very powerful, today on versions 5.5 is just available to MyISAM engine, but, like we can see on MySQL FullText documentation, it will be available also to InnoDB on MySQL 5.6

Usually when we want to search for a word or expression, we use LIKE ‘%word%’, in case we are looking for more than one word we use LIKE ‘%word1%word2%’, what many people don’t know is for this kind of search is expensive and not optimized to our MySQL, in this cases we solve our problems with FullText Index
the syntax is easy, MATHC() … AGAINST (), where MATCH we specified the name(s) of column(s) which we are looking for, yes, we can look for more then one column, we just need all this columns specified on our index and AGAINST is where we specify the word(s) which we are looking for, we can also, specified a search mode, but I will talk about it later

Let’s create our table and start it

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 ...');

Let’s do our first query, looking for articles which approach ‘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)

Now, let’s look for ‘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)

Now, let’s search for articles which approach ‘MySQL’, just a detail, all of our articles contain the word ‘MySQL’

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

Empty set (0.00 sec)

Why? Let’s talk about Search Modes, by default, MySQL uses Natural Language mode, which tell us if the searched word match if 50% or more rows, the entire query doesn’t match.
Other good function with MySQL allow us to use, is change our search mode, let’s use Boolean mode:

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, but what is the difference between they? why use boolean mode? like says on name, is true or false, let’s do a query looking for ‘database’ but I don’t want to show rows which contain the word ‘tutorial’, is this difficult? no, let’s see:

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)

Is this for today, on next article, we will learn more about the boolean mode, changing the order of relevance

Send to Kindle
This entry was posted in index, MySQL, performance. Bookmark the permalink.
  • http://www.wagnerbianchi.com/blog Wagner Bianchi

    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.

  • http://www.budhi.com.br Evert

    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.

      • http://www.budhi.com.br Evert

        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');

        • http://www.budhi.com.br Evert

          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

          • http://www.budhi.com.br Evert

            Valeu!

            Tentarei desta forma.

            Abs,