Full Text Searches and Strings - 5.18 Performing Phrase Searches with a FULLTEXT Index
(Page 4 of 4 )
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.
Performing Phrase Searches with a FULLTEXT Index
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.
* The use of COUNT( ) to produce multiple counts from the same set of values is described in Recipe 8.1.
† If you change ft_min_word_len, you must also use REPAIR TABLE to rebuild the indexes for all other tables thathave FULLTEXT indexes.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.
|
|
