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.

8 Responses to Using the Mysql FullText Index Search

  1. Parabéns pelo artigo sobre FullText Marcelo, mandou muito bem!! Vamo que vamo, happy mysql’ing!

  2. Cristiano says:

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

  3. Wazirpur says:

    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..

  4. Nehru Place says:

    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 !

  5. Leandro says:

    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

  6. Leandro says:

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

    Abraços

Leave a Reply

Your email address will not be published. Required fields are marked *


5 + three =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>