using "key phrases in quotes" with MATCH / SCORE

[mameha1977]

In MySQL I have a query using MATCH and SCORE.

There is one problem. When I use quotes in the query, it doesnt affect the results at all. Is there some problem with MATCH so that it ignores quotes?

This is my query. I get the same 30 results whether I use quotes or not, even though "big red product" should rank higher than "red man toy product".


SELECT *,
(MATCH (title) AGAINST ('\"red product\"' IN BOOLEAN MODE)*6)
+
(MATCH (documentCode, filename) AGAINST ('\"red product\"' IN BOOLEAN MODE)*6)
+
(MATCH (title_highlight, product_type, headline) AGAINST ('\"red product\"' IN BOOLEAN MODE)*3)
AS score
FROM documents
WHERE status = 'ACT' AND
MATCH(title, title_highlight, headline, product_type, documentCode, filename) AGAINST ('\"red product\"' IN BOOLEAN MODE)
ORDER BY score DESC;

[chazzy]

I hate to say it, but I believe that's the natural behavior. I'm thinking that you're expecting the "" around the phrase to some how group it, am i correct?

[mameha1977]

This confuses me.

I've never seen a decent search function that doesn't allow use of quotes to group phrases.

Do you have to do something like check for quotes in the query, if exist then use a normal SELECT query and if not use MATCH/SCORE?

[chazzy]

I think part of it is that you're assuming that what you enter in to the search box is exactly what you enter into the database. Based on what I understand about full text search (which, I don't claim to know much about), the following is the query you want to run to find all rows that match both red and product.


select... (with the appropriate fixes)
FROM documents
WHERE status = 'ACT' AND
MATCH(title, title_highlight, headline, product_type, documentCode, filename)
AGAINST ('+red +product' IN BOOLEAN MODE);

[mameha1977]

I found actually it does work OK with "quotes"

I just had to remove the backslashes to get it to work.

Interestingly, using + character has no effect though :)

[chazzy]

My answer's based on this page
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

So either you're using an older version of mysql, or one of the other conditions apply.