How do I increase Relevance value in an advanced MySQL query?

Question

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

Answer 1

First, I think it is redundant to use UPC = '123455778' again in the SELECT. You aren't going to get any rows where that is not true, so why not just use "1"?

Having said that I think your issue is order of operations. Enclosing the comparison in parenthesis works for me:

SELECT 
    `UPC`,
    (`UPC` = '12346790') * 16 AS Relevance
FROM 
    `products`
WHERE
    `UPC` = '12346790'
HAVING
    Relevance > 1
ORDER BY 
    Relevance DESC