Using the Mysql FullText Index Search

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

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’

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:

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:

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 !

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: