I have two records/fields:
record_no - product_name
1 - dummy
2 - dummy-to-go
I do a search like: select weight(), * from table where match(‘dummy’);
Is there a way to give a higher weight to an exact match (I use infix/prefix) as compared to a partial match (currently “dummy-to-go” gets a higher weight).
I tries using different weight algorithms but wonder if there is a better way to handle this.
Thanks
Roberto
currently “dummy-to-go” gets a higher weight
I can’t reproduce:
mysql> drop table if exists t; create table t(f text) min_infix_len='2'; insert into t values(1, 'dummy'), (2, 'dummy-to-go'); select weight(), * from t where match('dummy');
--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
create table t(f text) min_infix_len='2'
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
insert into t values(1, 'dummy'), (2, 'dummy-to-go')
--------------
Query OK, 2 rows affected (0.00 sec)
--------------
select weight(), * from t where match('dummy')
--------------
+----------+------+-------------+
| weight() | id | f |
+----------+------+-------------+
| 1356 | 1 | dummy |
| 1356 | 2 | dummy-to-go |
+----------+------+-------------+
2 rows in set (0.00 sec)
--- 2 out of 2 results in 0ms ---
Please modify this example, so the issue is clear.
Is there a way to give a higher weight to an exact match (I use infix/prefix
expand_keywords can help:
mysql> drop table if exists t; create table t(f text) min_infix_len='2' expand_keywords='1'; insert into t values(1, 'dummy'), (2, 'dummy-to-go'), (3, 'dumm'); select weight(), * from t where match('dumm');
--------------
drop table if exists t
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
create table t(f text) min_infix_len='2' expand_keywords='1'
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
insert into t values(1, 'dummy'), (2, 'dummy-to-go'), (3, 'dumm')
--------------
Query OK, 3 rows affected (0.00 sec)
--------------
select weight(), * from t where match('dumm')
--------------
+----------+------+-------------+
| weight() | id | f |
+----------+------+-------------+
| 1500 | 3 | dumm |
| 1409 | 1 | dummy |
| 1409 | 2 | dummy-to-go |
+----------+------+-------------+
3 rows in set (0.01 sec)
--- 3 out of 3 results in 0ms ---