Know Your MYSQL: FULLTEXT search using MATCH and AGAINST in MySQL

This concept comes under FULLTEXT index. If your table has FULLTEXT index in the fields having field types of varchar, char, text. It is faster to load fields having large data set in comparison to those who doesn’t have FULLTEXT index.

You need to create FULLTEXT index to a field or multiple fields to load or search text from tables.

Create index of FULLTEXT while creation

Create table `table_name` ( `field_name` varchar(100) FULLTEXT, FULLTEXT idx(`field_name`)) engine=InnoDB;

For this you need MySQL engine MyISM and from 5.6 and up we can also use InnoDB.

You can also create index by using Alter command

ALTER TABLE ` table_name` ADD FULLTEXT ( ` field_name ` ) ;

Now how to use MATCH() and AGAINST() in FULLTEXT search.

SELECT * FROM `table_name` WHERE MATCH(`field_name`) against(‘search_key’);

Formal Defination in MySQL Manual :

MATCH (col1,col2,…) AGAINST (expr [search_modifier])

search_modifier:

{

IN NATURAL LANGUAGE MODE

| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

| IN BOOLEAN MODE

| WITH QUERY EXPANSION

}