Hi!
I have a strange behavior on FULLTEXT index. The use case is a partner table, and I want to provide an auto-complete like experience to search on that table.
The query below is used to demonstrate the first issue:
select name, MATCH (name) AGAINST ('SENOR ABRAVANEL')
from partner
where MATCH (name) AGAINST ('SENOR ABRAVANEL')
and name = 'SENOR ABRAVANEL';
The exact same name can yield different relevance scores:
Why is that the case? I can assume that the relevance is calculated on each segment and is relative to the segment data?
The other situation is that the relevance behaves strangely in this situation:
select name, max(MATCH (name) AGAINST ('SENOR ABRAVANEL')) as relevance
from partner
where MATCH (name) AGAINST ('SENOR ABRAVANEL')
group by name
order by 2 desc;
The natural way for me here is that the exact match would have a better score than lines that have just one word match. In an auto-complete field if there is an exact match it would be better to show it on the first line. Also, because the results are in “random” order for the same relevance, the exact match can be last in the list and the user probably misses it.
Is this expected behavior?
As a workaround, this seems to work better, but is more painful to write that SQL and also for the database to execute it:
select * from (
select name,
max(MATCH (name) AGAINST ('SENOR')) as r1,
max(MATCH (name) AGAINST ('ABRAVANEL')) as r2
from partner
where MATCH (name) AGAINST ('SENOR ABRAVANEL')
group by name) as tt
order by r1+r2 desc
limit 30;
Any hints would be appreciated.