1

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.

AlexM
  • 325
  • 4
  • 11

1 Answers1

2

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thank you, I didn't even know up until now that there is such a thing as a stop word list. I will look into that. – AlexM Apr 28 '19 at 22:16
  • I have just seen your edit, may I ask what's bad about myisam (honestly, I have never considered to explicitly choose between the two because when I was starting out a few months ago, I did not know that different engines exist. Actually, I only got to learn it in the last few days) – AlexM Apr 28 '19 at 23:34
  • A bit of searching would reveal the answer: https://stackoverflow.com/questions/15678406/when-to-use-myisam-and-innodb – Shadow Apr 29 '19 at 01:06