I have a mysql table and need to perform a fulltext search on it. However, even though the required data is in the table, fulltext search does not retrieve it.
Here is the query:
SELECT * from declensions_english where match(finite) against("did" IN BOOLEAN MODE)
The searched after data looks like this:
I did
Of course this is in the column called finite. There are about a million rows in the table beside this one, so wildcards are very slow.
Why is this not working? It's not because of the length of the word (did), because I've already set ft_min_word_len to 1. There are other cases with three letter words that deliver the expected outcome (i. e. the data is retrieved). But there are also cases where even four letter words are not found. I have no idea what's going on, but I am only using fulltext search since yesterday, so consider me a newbie here.
Since you use ft_min_word_len, I must assume that your table uses myisam table engine.
The word did is on the myisam stop word list, this is why the search does not return it.
You can either disable or change the stop word list or migrate to innodb, which does not have this word on its default stop word list.
To be honest, I cannot think of any reason to use myisam in 2019. You really should migrate over to innodb.