Return better results using full-text search in MySQL

by Contributor Melonfire | Mar 16, 2006 6:31:00 PM

Tags: OPEN SOURCE, Databases, Contributor Melonfire, MySQL, full-text search

Takeaway: This document will show you how to perform full-text searches in MySQL.

Many Web applications offer full-text search capabilities, wherein the user can locate matching records by using a word or word fragment as the query term. Behind the scenes, these applications are often performing this search by using a LIKE clause in a SELECT query. While this certainly works, it is an extremely inefficient way of performing a full-text search, especially with large volumes of data.

MySQL offers a solution to this problem, in the form of built-in full-text search. Here, the developer need simply mark certain fields as needing full-text search, and then use special MySQL functions to run searches on those fields. This is not only more performance-efficient (because MySQL indexes these fields differently to optimize the search) but also results in higher-quality searches, because MySQL intelligently ranks the results using a natural-language algorithm to remove irrelevant items.

This document will show you how to perform full-text searches in MySQL.

1. Setting up the base table

Begin by creating a sample table, using the SQL commands below:

mysql> CREATE TABLE reviews (id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT, data TEXT);

The command above creates a single table for music album reviews (which are essentially paragraphs of text). Populate this table with some sample records:

mysql> INSERT INTO `reviews` (`id`, `data`) VALUES (1, 'Gingerboy has a new single out called Throwing Rocks. It\'s great!'); mysql> INSERT INTO `reviews` (`id`, `data`) VALUES (2, 'Hello all, I really like the new Madonna single. One of the hottest tracks currently playing...I\'ve been listening to it all day'); mysql> INSERT INTO `reviews` (`id`, `data`) VALUES (3, 'Have you heard the new band Hotter Than Hell? They have five members and they burn their instruments when they play in concerts. These guys totally rock! Like, awesome, dude!');

Verify that the data has been correctly entered:

mysql> SELECT * FROM reviews;
+----+--------------------------------------------+
| id | data |
+----+--------------------------------------------+
| 1 | Gingerboy has a new single out called ... |
| 2 | Hello all, I really like the new Madon ... |
| 3 | Have you heard the new band Hotter Than... |
+----+--------------------------------------------+
3 rows in set (0.00 sec)

2. Define the full-text search fields

Next, define the field you intend to run searches on as a full-text index:

mysql> ALTER TABLE reviews ADD FULLTEXT INDEX (data);
Query OK, 3 rows affected (0.21 sec)
Records: 3 Duplicates: 0 Warnings: 0

Check that the index has been added with a quick SHOW INDEXES command:

3. Perform a full-text search

Once you've got your data in and indexed, it's time to put MySQL's full-text search through its paces. The simplest form of full-text search is a SELECT query with a MATCH...AGAINST clause. Here's an example which locates records containing the word "single":

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('single');+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

Here,MATCH() looks in the field passed to it as argument for text matching the argument passed to AGAINST(). Matches, if any, are returned in the normal way. Note that you can pass MATCH() more than one field to look in—simply separate the field list with commas.

When MySQL receives a full-text search request, it internally ranks each record with a numerical score. Records without a match receive a zero score, while records with a "more relevant" match receive a relatively higher score than those with a "less relevant" match. Relevance is determined by MySQL using a variety of different criteria; look at the MySQL manual for more information on how this works.

To see how each record is ranked, simply return the MATCH() function as part of your result set, as below:

mysql> SELECT id, MATCH (data) AGAINST ('rock') FROM reviews;
+----+-------------------------------+
| id | MATCH (data) AGAINST ('rock') |
+----+-------------------------------+
| 1 |                             0 |
| 2 |                             0 |
| 3 |               1.3862514533815 |
+----+-------------------------------+
3 rows in set (0.00 sec)

4. Use Boolean search modifiers

You can also use Boolean search modifiers for a more precise search, by adding the special IN BOOLEAN MODE modifier in the AGAINST clause. Here's an example which finds records containing the word "single" but not the word "Madonna":

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('+single -madonna' IN BOOLEAN MODE);
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

The most common application of this search feature is to have MySQL search for word fragments (instead of complete words) by using the IN BOOLEAN MODE clause with the * (asterisk) operator. Here's an example, which finds all records containing the word fragment "hot":

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('hot*' IN BOOLEAN MODE);+----+
| id |
+----+
| 3 |
| 2 |
+----+
2 rows in set (0.00 sec)

You can also use this to search for records containing at least one of the arguments passed to AGAINST. The following example looks for records containing at least one of the words "hell" and "rocks":

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('hell rocks' IN BOOLEAN MODE);
+----+
| id |
+----+
| 1 |
| 3 |
+----+
2 rows in set (0.00 sec)

As the examples above illustrate, full-text search provides an efficient alternative to the traditional SELECT...LIKE statement. Try it for yourself the next time you sit down to write a search interface to your MySQL database!

2 comment(s)