Home / Indexes / Full Text Indexes / Question No: 182

I want to use MySQL fulltext feature. Can you explain, how to use natural language full-text search in MySQL?

MySQL provides MATCH() and AGAINST() functions to perform full-text searches. By default, MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST() function. For natural-language full-text searches, it is required that the columns named in the MATCH() function must be same columns included in FULLTEXT index as you created in your table. So, if you want to search the title or body columns separately, you would need to create separate FULLTEXT indexes for each column.

By default, the search is performed in case-insensitive fashion. In MySQL 4.1 and up, you can make a full-text search by using a binary collation for the indexed columns. For example, a column that has a character set of latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches.

Let's take an example easy to understand copied from mysql.com:

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
       );

INSERT INTO articles (title,body) 
      VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),
      ('How To Use MySQL Well','After you went through a ...'),
      ('Optimizing MySQL','In this tutorial we will show ...'),
      ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
      ('MySQL vs. YourSQL','In the following database comparison ...'),
      ('MySQL Security','When configured properly, 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 ...             |
+----+-------------------+------------------------------------------+