How do I increase Relevance value in an advanced MySQL query?
- by morgant
I've got a MySQL query similar to the following:
SELECT *, MATCH (`Description`) AGAINST ('+ipod +touch ' IN BOOLEAN MODE) * 8 + MATCH(`Description`) AGAINST ('ipod touch' IN BOOLEAN MODE) AS Relevance
FROM products WHERE ( MATCH (`Description`) AGAINST ('+ipod +touch' IN BOOLEAN MODE) OR MATCH(`LongDescription`) AGAINST ('+ipod +touch' IN BOOLEAN MODE) )
HAVING Relevance > 1
ORDER BY Relevance DESC
Now, I've made the query more advanced by also searching for UPC:
SELECT *, MATCH (`Description`) AGAINST ('+ipod +touch ' IN BOOLEAN MODE) * 8 + MATCH(`Description`) AGAINST ('ipod touch' IN BOOLEAN MODE) + `UPC` = '123456789012' * 16 AS Relevance
FROM products WHERE ( MATCH (`Description`) AGAINST ('+ipod +touch' IN BOOLEAN MODE) OR MATCH(`LongDescription`) AGAINST ('+ipod +touch' IN BOOLEAN MODE) ) AND `UPC` = '123456789012'
HAVING Relevance > 1
ORDER BY Relevance DESC
That'll return results, but the fact that I had a successful match on the UPC does not increase the value of Relevance. Can I only do that kind of calculation w/full text searches like MATCH() AGAINST()?
Clarification: Okay, so my real question is, why does the following not have a Relevance = 16?
SELECT `UPC`, `UPC` = '123456789012' * 16 AS Relevance FROM products WHERE `UPC` = '123456789012' HAVING Relevance > 1 ORDER BY Relevance DESC