Assigning relevance to fulltext search

[spook007]

I've been experimenting with fulltext search I've been able to get relevance level on the words that I try to match, but is there any way to assign relevance to the field that the word is coming from.

SELECT * FROM basket WHERE MATCH(fruit, vegtable)
AGAINST ('>apple carrot' IN BOOLEAN MODE);

in the above example... is there any way to assign relevance priority for apple coming from field fruit instead of vegetable field?

 

[janbednar]

I have troubles with the same question as you, but as your thread is already kind a old I thougth you may allready know the answer. If you do so, could you, please, let me know?

 

[piti]

i think this can work, but will add aditional processing

you could add a new fulltext indexes to the columns separately and use some function to calculate the relevance of your query

e.g.
fulltext index 1: column1, column2
fulltext index 2: column1
fulltext index 3: column2

now in your select statement match your query against all these indexes with returning relevances

select tablename.*, (match (column1,column2) against ('string'))*1 + (match (column1) against ('string'))*2 + (match (column2) against ('string'))*1 As RelevanceValue
from tablename
where match (column1,column2) against ('string')
order by RelevanceValue desc

now the relevance from the both columns index has a weight of 1, column1 has weight 2 and column2 weight 1
you can of course create your own rule, this is just an idea

 

[janbednar]

Thanks for your nice idea