Using a FULLTEXT Search with Short Words
Problem
FULLTEXT searches for short words return no
records.
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 using a MySQL 3.23 server,
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 records). 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 records, and by using SQL pattern matches to count the number of records containing each word:[27]
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" | +--------------+-------------------------+-------------------------+ ...