Mysql full text search cannot find a word - why?

Question

(mysql 5.1.36)

I'm trying to find out why when I search for the word 'three' in a full-text query I get no results. I get the same result returned if the word 'three' is the only text in a field, or if it is part of a sentence.

It is easy to reproduce:

CREATE TABLE `test`( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `word` TEXT NOT NULL , PRIMARY KEY (`id`) ); 
ALTER TABLE `test` ADD FULLTEXT `NewIndex1` (`word`);
INSERT INTO `test`(`id`,`word`) VALUES ( NULL,'three');
INSERT INTO `test`(`id`,`word`) VALUES ( NULL,'thorn');

Then run these queries:

SELECT * FROM test WHERE word='three';

result = [ 1, 'three' ]

SELECT word FROM test WHERE MATCH(word) AGAINST ('three*' IN BOOLEAN MODE );

result = []

SELECT word FROM test WHERE MATCH(word) AGAINST ('three' IN BOOLEAN MODE);

result = []

SELECT word FROM test WHERE MATCH(word) AGAINST ('thre*' IN BOOLEAN MODE );

result = []

SELECT word FROM test WHERE MATCH(word) AGAINST ('thorn*' IN BOOLEAN MODE )

result = [ 2, 'thorn' ]

SELECT word FROM test WHERE MATCH(word) AGAINST ('thorn' IN BOOLEAN MODE)

result = [ 2, 'thorn' ]

SELECT word FROM test WHERE MATCH(word) AGAINST ('thor*' IN BOOLEAN MODE)

result = [ 2, 'thorn' ]

Why does the full-text search for 'three' return no results?

Answer 1

Because three is a stop word, fulltext will ignore the word and return no result related to the word 'three'. Basically, stop words are present to increase performance and do not return every row that contains words like 'is, are, there, be...'. Here is the list of stopwords, check them.

Answer 2

To add to the above answer: If you do want to add the stopwords for full text indexing, Just add this to your .cnf file, restart MySQL engine and rebuild indexes: ft_stopword_file = "" I addition to that, if you haven't already, lower the min word to 3 (ft_min_word_len=3) to be able to search for 3 letter words.