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?
Read https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html:
I recommend reading the rest of the documentation about fulltext indexes if you are going to use them.