Full-Text Search in MySQL

In this post i am going to explore basic of this feature. Following is the table i am going to use for FULL-TEXT search explanation.

SELECT * FROM articles;
+—-+———————–+——————————————+
| 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 … |
| 7 | Database Theory | I am going to teach you database theory |
+—-+———————–+——————————————+

Whle creating table i have used FULLTEXT (title,body) to allow full text search on these columns. Behind the scene it creates a index on these columns.

Lets do full-text search for string ‘database’.

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
+—-+——————-+——————————————+
| id | title | body |
+—-+——————-+——————————————+
| 7 | Database Theory | I am going to teach you database theory |
| 1 | MySQL Tutorial | DBMS stands for DataBase … |
| 5 | MySQL vs. YourSQL | In the following database comparison … |
+—-+——————-+——————————————+

FULL TEXT allows different mode to run search query, here it is IN NATURAL LANGUAGE MODE.

• It means search string is a natural human language.

• By default full-text search is case-insensitive.

• Minimum length of word to be found is 3, which can be manipulated with innodb_ft_min_token_size .
• The returned rows are sorted with highest relevance first. relevance is kind of score is calculated by MYSQL internally.

Above query search search string in title and description. Now let search in title only.
SELECT * FROM articles WHERE MATCH (title) AGAINST ('database' IN NATURAL LANGUAGE MODE);
since we have defined the full-text search on title and body, To search only in column body we need to define FULL-TEXT index on that column.

Lets now do full-text search for string ‘to’.

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('to' IN NATURAL LANGUAGE MODE);

No result, but ‘to’ sting exist in body with id=7 ?
This is because full-text search will skip certain words, which are refereed as stop-words.
You can find out list for stop-words like:
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+——-+
| value |
+——-+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
| how |
| i |
| in |
| is |
| it |
| la |
| of |
| on |
| or |
| that |
| the |
| this |
| to |
| was |
| what |
| when |
| where |
| who |
| will |
| with |
| und |
| the |
| www |
+——-+

What if you want specific case of search, like get all result having string ‘database’ and not having ‘MySQL’. . That is where BOOLEAN mode of full-text search helps.

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+database -MySQL' IN BOOLEAN MODE);
+—-+—————–+—————————————–+
| id | title | body |
+—-+—————–+—————————————–+
| 7 | Database Theory | I am going to teach you database theory |
+—-+—————–+—————————————–+

more boolean modes:

• find all rows which have atleast one of ‘MySQL’ and ‘YourSQL’
  SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘MySQL YourSQL’ IN BOOLEAN MODE);
  +—-+———————–+——————————————+
  | id | title | body |
  +—-+———————–+——————————————+
  | 5 | MySQL vs. YourSQL | In the following database comparison … |
  | 6 | MySQL Security | When configured properly, MySQL … |
  | 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. … |
  +—-+———————–+——————————————+

• find rows that contains both words
  SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘+MySQL +YourSQL’ IN BOOLEAN MODE);
  +—-+——————-+——————————————+
  | id | title | body |
  +—-+——————-+——————————————+
  | 5 | MySQL vs. YourSQL | In the following database comparison … |
  +—-+——————-+——————————————+

Similar kind of search can be made using Boolean mode.

So this was very basic of full-text search, hope you find it helpful.