Mysql full-text search cannot find a word - why?

Question

(mysql 5.1.36)

I am 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 if the word “three” is the only text in the field or is part of a sentence.

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 the following 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 a full-text search for three not return results?

Answer 1

Since the three words are stop words, the full text will ignore the word and not return the result associated with the word “three”. In principle, stop words are present to improve performance and do not return every line containing words like "yes, yes, yes ...". Here is a list in seconds, check them out.

Answer 2

To add to the above answer: If you want to add stop words to completely index the text, just add this to your .cnf file, restart the MySQL engine and rebuild the indexes: ft_stopword_file = "" In addition, if you haven’t done so already, leave the minimum word up to 3 ( ft_min_word_len=3 ) to search for three letter words.