MariaDB MATCH AGAINST using + in BOOLEAN MODE

Question

I am having a problem with searching a MariaDB database.

Search keywords come from user-input, processed with PHP, SQL query made and results returned.

In it's simplest form :-

 CREATE TABLE db (
 item_no SMALLINT(6) NOT NULL,
 item_text MEDIUMTEXT,
 PRIMARY KEY (item_no),
 FULLTEXT (item_text))
 ENGINE=innodb";

I want to do an AND keyword search, such that more keywords give fewer results. To do this I use the MATCH AGAINST query IN BOOLEAN MODE, putting a '+' prefix to each keyword :-

 SELECT item_no FROM db WHERE MATCH (item_text) AGAINST ('+wordone +wordtwo' IN BOOLEAN MODE)

This words fine, as do literal searches :-

 SELECT item_no FROM db WHERE MATCH (item_text) AGAINST ('"wordone the wordtwo"' IN BOOLEAN MODE)

... however, if the last example is unquoted as :-

 SELECT item_no FROM db WHERE MATCH (item_text) AGAINST ('+wordone +the +wordtwo' IN BOOLEAN MODE)

I get no results. Is it because 'the' is a stopword ?

I am running MariadB 10.3.34 on Linux Mint 20.1.

• Rick James: (Your second query should give different results when the words are in a different order. That is, your 3 queries are not equivalent.)
• Don Gibbs: Thanks @Rick for confirming this is a stopword issue. To disable the stopword functionality, I eventually found the magic: SET GLOBAL innodb_ft_enable_stopword = 'OFF' which does the trick. I could not set innodb_ft_enable_stopword in any configuration file, MariaDB says 'unknown variable' - a problem for another day.
• Rick James: Hmmm... I see it here: mariadb.com/kb/en/innodb-system-variables/… -- But, being "Scope: Global" probably means that you need to be root to set it and it won't take effect until the user logs in again.

Answer 1

I was able to get around this issue by adding this text

ft_stopword_file = ""

to the .my.cnf file for MariaDB. I'd like to know why it's not working like it used to though.

Answer 2

True. It cannot find any rows with "the" because "the" was never indexed because it is a 'stopword'. The same goes for "words" that are shorter than innodb_ft_min_token_size, which defaults to 3.

I suggest you post a bug at bugs.mysql.com suggesting that "+" in front of a stopword or short word should not invalidate the entire query, but should simply remove the word from consideration. (I think this is the way MyISAM's FULLTEXT used to work.)

• dbdemon: As the question relates to MariaDB, the bug report should probably go to jira.mariadb.org :)