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)