0

Relevance score of MATCH..AGAINST is not working.

Created one dummy table which has 2 rows.

Dummy Table

Row1=> 'Leela Hayat Marriot'

Row2=> 'Americas Best Value'

Query1:

SELECT MATCH (col1) AGAINST ('Leela* Hayat*' IN BOOLEAN MODE) AS relevance FROM table1 WHERE MATCH (col1) AGAINST ('Leela* Hayat*' IN BOOLEAN MODE);

Result:

relevance

2

Query2:

SELECT MATCH (col1) AGAINST ('Americas* Best*' IN BOOLEAN MODE) AS relevance FROM table1 WHERE MATCH (col1) AGAINST ('Americas* Best*' IN BOOLEAN MODE);

Result:

relevance

1

Query1 is working fine but why is query 2 not working?

Why am I getting relevance 1 instead of 2 in Query2 as Americas and Best both are present in column.

Thanks

Sasa
  • 129
  • 2
  • 9
  • show your schema create. Tell us your mysql version. – Drew Jun 29 '16 at 19:01
  • @Drew: CREATE TABLE `table1` ( `col1` varchar(500) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ===================================== INSERT INTO `table1` (col1) VALUES ('Leela Hayat Marriot'), ('Americas Best Value'); ===================================== ALTER TABLE `table1` ADD FULLTEXT KEY `col1` (`col1`); ======================== Mysql Version: 5.5.46 ====================== Sorry for unformatted comment – Sasa Jun 30 '16 at 07:35

1 Answers1

0

http://dev.mysql.com/doc/refman/5.7/en/fulltext-stopwords.html

'BEST' is listed in stopword list.

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_ft_stopword_file

ft_stopword_file:

The file from which to read the list of stopwords for full-text searches on MyISAM tables. The server looks for the file in the data directory unless an absolute path name is given to specify a different directory. All the words from the file are used; comments are not honored. By default, a built-in list of stopwords is used (as defined in the storage/myisam/ft_static.c file). Setting this variable to the empty string ('') disables stopword filtering.

I disabled stopwords list and now query 2 is working fine.

Thanks for help.

Sasa
  • 129
  • 2
  • 9
  • A few I wrote up: [1](http://stackoverflow.com/a/30677347) and [2](http://stackoverflow.com/a/37604988) depending if you want MyISAM or INNODB – Drew Jul 02 '16 at 18:03