CODEWITHSUNDEEP
mysqlfull-text-search
Chris Muench
Chris Muench

Reputation: 18328

mysql fulltext search relevancy score not right

I have a fulltext index on the name column. When I do a search for ficus nit 15g* in boolean mode I would expect Ficus Nitida - Indian Laurel Fig to be the top result. But all 3 items have same relevance score.

Is there any way to have more relevant result at the top?

Below is query with results.

mysql> SELECT phppos_items.name, MATCH (phppos_items.name) AGAINST ('ficus nit 15g*' IN BOOLEAN MODE) as rel FROM `phppos_items` WHERE MATCH (phppos_items.name) AGAINST ('ficus nit 15g*' IN BOOLEAN MODE)  ORDER BY `rel` DESC;
+-----------------------------------------+--------------------+
| name                                    | rel                |
+-----------------------------------------+--------------------+
| Ficus Benjamna - Weeping Banyon - 15gal | 0.0984337329864502 |
| Ficus Rubignosa - Rusty Leaf Fig 15gal  | 0.0984337329864502 |
| Ficus Nitida - Indian Laurel Fig 15gal  | 0.0984337329864502 |
+-----------------------------------------+--------------------+
3 rows in set (0.00 sec)

EDIT: tried answer

mysql> SELECT phppos_items.name, MATCH (phppos_items.name) AGAINST ('ficus* nit* 15g*' IN BOOLEAN MODE) as rel FROM `phppos_items` WHERE MATCH (phppos_items.name) AGAINST ('ficus* nit* 15g*' IN BOOLEAN MODE)  ORDER BY `rel` DESC;
+-----------------------------------------+--------------------+
| name                                    | rel                |
+-----------------------------------------+--------------------+
| Ficus Benjamna - Weeping Banyon - 15gal | 0.1812381148338318 |
| Ficus Rubignosa - Rusty Leaf Fig 15gal  | 0.1812381148338318 |
| Ficus Nitida - Indian Laurel Fig 15gal  | 0.1812381148338318 |
+-----------------------------------------+--------------------+
3 rows in set (0.00 sec)

Upvotes: 1

Views: 958

Answers (1)

Solarflare
Solarflare

Reputation: 11106

Unless you are using a wildcard, the fulltext search only matches (and scores) on identical words. Nitida and nit are different words, and neither contribute to the score nor would they actually be found, e.g. match(...) against ('nit') would not return any of your sample rows.

Depending on your exact requirements, it might already be enough to append a wildcard to each search term, which will both find and score partial words, e.g. use

match(...) against ('ficus* nit* 15g*' IN BOOLEAN MODE)

This will not make exact matches more valuable than partial matches though, so you could also customize your score, e.g. match against both nit and nit* but weight the exact match higher, using something like 

match(...) against ('>ficus >nit >15g ficus* nit* 15g*' IN BOOLEAN MODE) as rel

using the > <-operator:

These two operators are used to change a word's contribution to the relevance value that is assigned to a row. The > operator increases the contribution and the < operator decreases it.

This way, an exact match to nit will contribute more, while an inexact match to Nitida will still add some points. A thing to note is that the relevance is based on the rarity of the word, so a rare but wildcarded word might still be more relevant than the common but exact match.

You gain more control with a custom weight using something like 

match(...) against ('ficus nit 15g' IN BOOLEAN MODE) * 10 
+ match(...) against ('ficus* nit* 15g*' IN BOOLEAN MODE) as rel

You can do the scoring independently from the search, but you can/have to decide if you want to find Nitida if you enter Nit, or if you just want to give it a better score (but only find it if has an exact match with a different search term). E.g. you would use either WHERE MATCH(...) AGAINST ('ficus nit 15g*' IN BOOLEAN MODE) or WHERE MATCH(...) AGAINST ('ficus* nit* 15g*' IN BOOLEAN MODE).

Upvotes: 2

Related Questions