MySQL Cookbook, 2nd Edition

Performing Phrase Searches with a FULLTEXT Index

Problem

You want to perform a FULLTEXT search for a phrase; that is, for words that occur adjacent to each other and in a specific order.

Solution

Use the FULLTEXT phrase-search capability.

Discussion

To find rows that contain a particular phrase, you can’t use a simple FULLTEXT search:

mysql>SELECT COUNT(*) FROM kjv
    -> WHERE MATCH(vtext) AGAINST('still small voice');
+----------+
| COUNT(*) |
+----------+
|      548 |
+----------+

The query returns a result, but it’s not the result you’re looking for. A FULLTEXT search computes a relevance ranking based on the presence of each word individually, no matter where it occurs within the vtext column, and the ranking will be nonzero as long as any of the words are present. Consequently, this kind of statement tends to find too many rows.

FULLTEXT searching supports phrase searching in Boolean mode. To use it, place the phrase in double quotes within the search string:

mysql>SELECT COUNT(*) FROM kjv
    -> WHERE MATCH(vtext) AGAINST('"still small voice"' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

A phrase match succeeds if a column contains the same words as in the phrase, in the order specified.