CODEWITHSUNDEEP
mysqlfull-text-searchfull-text-indexing
tester
tester

Reputation: 3987

MySQL Fulltext MATCH/AGAINST showing not always results

I have the following table setup:

CREATE TABLE IF NOT EXISTS `search_table` (
  `fulltext_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `data_index` longtext COMMENT 'Data index',
  PRIMARY KEY (`fulltext_id`),
  FULLTEXT KEY `FTI_CATALOGSEARCH_FULLTEXT_DATA_INDEX` (`data_index`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Search table' 
AUTO_INCREMENT=1;

INSERT INTO `search_table` (`fulltext_id`, `data_index`)
VALUES (1, 'Test Hello abc');

Then I try a full text search on it with 3 different query texts:

SELECT `s`.`fulltext_id`, MATCH (s.data_index) AGAINST ('Test' IN BOOLEAN MODE) AS `relevance` FROM `search_table` AS `s`
 WHERE (MATCH (s.data_index) AGAINST ('Test' IN BOOLEAN MODE));

SELECT `s`.`fulltext_id`, MATCH (s.data_index) AGAINST ('Hello' IN BOOLEAN MODE) AS `relevance` FROM `search_table` AS `s`
 WHERE (MATCH (s.data_index) AGAINST ('Hello' IN BOOLEAN MODE));

SELECT `s`.`fulltext_id`, MATCH (s.data_index) AGAINST ('abc' IN BOOLEAN MODE) AS `relevance` FROM `search_table` AS `s`
 WHERE (MATCH (s.data_index) AGAINST ('abc' IN BOOLEAN MODE));

Only the first query (the search for Test) gives a result back, the other two not. I don't understand why?

Upvotes: 0

Views: 348

Answers (1)

andrews
andrews

Reputation: 2173

You should check the list of currently defined stopwords. You can do this like this:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

More information about MySQL stopwords can be found here: https://dev.mysql.com/doc/refman/5.7/en/fulltext-stopwords.html

Hello for example is a known stopword therefore it is being ignored during FTS matching. If you check comments on the linked MySQL doc page you will find links from user to English language stopwords lists, like https://www.ranks.nl/stopwords/.

Note, MySQL as well as other DB engines allows you to specify your own custom list of stop words. So you should check both pre-defined system stopwords list and any existing custom stopwords lists.

Upvotes: 1

Related Questions