0

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.

3
  • (Your second query should give different results when the words are in a different order. That is, your 3 queries are not equivalent.)
    – Rick James
    Nov 5 at 17:05
  • 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.
    – Don Gibbs
    Nov 7 at 12:31
  • 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.
    – Rick James
    Nov 7 at 16:12

1 Answer 1

0

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.)

1
  • 1
    As the question relates to MariaDB, the bug report should probably go to jira.mariadb.org :)
    – dbdemon
    Nov 6 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.