Database Reference
In-Depth Information
mysql>
SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');
+----------+
| COUNT(*) |
+----------+
| 3892 |
+----------+
mysql>
SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('sin');
+----------+
| COUNT(*) |
+----------+
| 389 |
+----------+
That's better!
But why do the
MATCH()
queries find 3,892 and 389 rows, whereas the earlier
LIKE
queries
find 4,117 and 1,292 rows? That's because the
LIKE
patterns match substrings and the
full-text search performed by
MATCH()
matches whole words.
If your
kjv
table uses the InnoDB storage engine, you won't see the behavior just de‐
scribed because the default word length is 3 to begin with. However, specific values aside,
similar principles apply:
• There is a minimum word length parameter,
innodb_ft_min_token_size
in this
case.
• You can set that parameter at startup. If you change it from its previous value, you
should rebuild all InnoDB table
FULLTEXT
indexes. InnoDB does not support
RE
PAIR
TABLE
, but you can drop and re-create each index. For example:
mysql>
ALTER TABLE kjv DROP INDEX vtext, ADD FULLTEXT (vtext);
5.14. Requiring or Prohibiting Full-Text Search Words
Problem
You want to require or prohibit specific words in a full-text search.
Solution
Use a Boolean mode search.
Discussion
Normally, full-text searches return rows that contain any of the words in the search
string, even if some of them are missing. For example, the following statement finds
rows that contain either of the names David or Goliath:
