I am using WAMP on my computer for a searchable database which utilizes a MySQL query that looks like this:
SELECT * FROM table_name WHERE MATCH (field_01, field_02, field_03, field_04, field_05, field_06, field_07, field_08, field_09) AGAINST('\"$searchterm\"' IN BOOLEAN MODE) ORDER BY field_01,field_10,field_11,field_12 ASC
I have a full index with fields 01 to 09, and this setup has worked nicely on my previous computer (WAMP installation at least 5 years old).
However, on my new computer (WAMP installation with MySQL version 5.7.31) it only works selectively, i.e. it sometimes returns no result.
I have researched the issue and stopwords seemed like the key to the solution because the zero-result issue occurs for search words like 'why', 'for', 'and', 'or'.
I have tried the ft_stopword_file="" method (restarting WAMP and repairing the table). Didn't help.
And the strange thing is that the very same query works for the search word 'what' and several other stopwords listed in the MySQL documentation.
At this point I am unable to see any logic to the behavior of this query and I hope someone can point out what I may have missed. Thank you.
Since the word "what" works for you, which is a stopword for both InnoDB and MyISAM, this issue doesn't seem to be related to stopwords (or at least not after you disabled the stopword list).
The most likely reason is that short words are not indexed. Check your setting for ft_min_word_len, which defaults to a minimum word lenght of 4 (for MyISAM) and would thus exclude e.g. for but not what, matching your situation. After changing it, you have to recreate your fulltext index (again). You may have adjusted that setting on your old server and forgot about it, so check it there too if that server is still available.
Or maybe you used InnoDB there, where the corresponding setting innodb_ft_min_token_size defaults to 3, so most of your test words would have worked out-of-the-box. Also check that value on your new server in case you are currently using InnoDB. Although I assume you would have remembered explicitly increasing that value to 4, it might be a default setting in your wamp setup for some reason.