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, or
combine a non-phrase FULLTEXT search with regular
pattern matching.
Discussion
To find records 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
query tends to find too many records.
As of MySQL 4.0.2, FULLTEXT searching supports
phrase searching in Boolean mode. To use it, just place the phrase
within double quotes.
mysql>SELECT COUNT(*) FROM kjv->WHERE MATCH(vtext) AGAINST('"still small voice"' IN BOOLEAN MODE);+----------+ | COUNT(*) | +----------+ | 1 | +----------+
Prior to 4.0.2, a workaround is necessary. You could use an
IN BOOLEAN
MODE search to require each word to be present,
but that doesn’t really solve the problem, because
the words can still occur in any order:
mysql>SELECT COUNT(*) FROM kjv->WHERE MATCH(vtext)->AGAINST('+still +small +voice' IN ...