relevance score of MATCH..AGAINST is not working (MySql)

0
votes

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

1
mysql
show your schema create. Tell us your mysql version. - Drew
@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

1 Answers

0
votes

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.