I have a table squirrel_schools that has a FULLTEXT index on the keywords field (among others).

I'm doing a BOOLEAN search against two of the words in keywords and getting a score of zero, which doesn't make sense:

SELECT record_id, keywords, (MATCH (keywords) AGAINST (" l4  3rx" IN BOOLEAN MODE)) AS score 
FROM squirrel_schools WHERE record_id = 46465;
 ----------- --------------------------- ------- 
| record_id | keywords                  | score |
 ----------- --------------------------- ------- 
|     46465 | l4 3rx l43rx 46465 104651 |     0 |
 ----------- --------------------------- ------- 
1 row in set (0.017 sec)

As you can see, both "l4" and "3rx" are present in that column. When the data is indexed, with this particular thing (a postcode), I also concatenate it and save that into keywords too. Searching for the concatenated form DOES work:

SELECT record_id, keywords, (MATCH (keywords) AGAINST (" l43rx" IN BOOLEAN MODE)) AS score 
FROM squirrel_schools WHERE record_id = 46465;
 ----------- --------------------------- -------------------- 
| record_id | keywords                  | score              |
 ----------- --------------------------- -------------------- 
|     46465 | l4 3rx l43rx 46465 104651 | 22.382286071777344 |
 ----------- --------------------------- -------------------- 

(When I say 'it works' i mean it gives it a score higher than zero)

Can anyone explain what's going on here? Is there some hidden rule that is breaking the search, in the first example?

I'm using MYSQL 5.6.51. Thanks.

EDIT: i now suspect it breaks if I include any search word that is 2 characters long, or less. Is that a general rule?

CodePudding user response：

Read https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html:

Configuring Minimum and Maximum Word Length

The minimum and maximum lengths of words to be indexed are defined by the innodb_ft_min_token_size and innodb_ft_max_token_size for InnoDB search indexes, and ft_min_word_len and ft_max_word_len for MyISAM ones. After changing any of these options, rebuild your FULLTEXT indexes for the change to take effect. For example, to make two-character words searchable, you could put the following lines in an option file:

[mysqld]
innodb_ft_min_token_size=2
ft_min_word_len=2

Then restart the server and rebuild your FULLTEXT indexes.

I recommend reading the rest of the documentation about fulltext indexes if you are going to use them.