MySQL Cookbook, 2nd Edition

Using a FULLTEXT Search with Short Words

Problem

FULLTEXTsearches for short words return no rows.

Solution

Change the indexing engine’s minimum word length parameter.

Discussion

In a text like the KJV, certain words have special significance, such as “God” and “sin.” However, if you perform FULLTEXT searches on the kjv table for those words, you’ll observe a curious phenomenon—both words appear to be missing from the text entirely:

mysql>SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('sin');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

One property of the indexing engine is that it ignores words that are “too common” (that is, words that occur in more than half the rows). This eliminates words such as “the” or “and” from the index, but that’s not what is going on here. You can verify that by counting the total number of rows, and by using SQL pattern matches to count the number of rows containing each word:^[9]

mysql>SELECT COUNT(*) AS 'total verses',
    -> COUNT(IF(vtext LIKE '%God%',1,NULL)) AS 'verses containing "God"',
    -> COUNT(IF(vtext LIKE '%sin%',1,NULL)) AS 'verses containing "sin"'
    -> FROM kjv; +--------------+-------------------------+-------------------------+ | total verses | verses containing "God" | verses containing "sin" | +--------------+-------------------------+-------------------------+ | 31102 | 4118 | 1292 | +--------------+-------------------------+-------------------------+ ...