MySQL Forums
Forum List  »  Full-Text Search

Unsatisfying results for Binary Mode exact phrases
Posted by: Jerry Seeger
Date: June 09, 2009 08:32PM

I'm using a MySQL 5.0 install which I share, and do not control. Perhaps later releases have addressed some of this, and I'll just have to wait until my host catches up with the times. Reading the literature on the subject, though, I'm not optimistic.

There are two issues - one causes too many results, the other not enough.

First, when I search on "Pasadena, California" I get results that include either of the words:

SELECT ID,post_title,post_content,post_excerpt,post_date, 
MATCH(post_title,post_content) AGAINST ('"Pasadena, California"' IN BOOLEAN MODE) AS score FROM wp_posts 
WHERE MATCH (post_title,post_content) AGAINST ('"Pasadena, California"' IN BOOLEAN MODE) 
AND post_status = 'publish' AND (post_type='post' OR post_type = 'page') ORDER BY score DESC

Not only are rows without and exact match returned, results are ranked on frequency of any word, so a match that uses "California" three times but never uses "Pasadena" takes precedence over one that includes the exact phrase.

Second, when I search on "You don't have to thank me" I get no results at all! It seems to me that even if a phrase is all stopwords, that six stopwords in a particular order should match. In fact, I'd go so far as to say that only really common two-word phrases should be stopped. It seems obvious enough to me that I wonder if the most recent MySQL versions already act this way.

I tried both the above situations on unindexed tables as well, to see if word order (not reflected in the index) would be better respected, but no luck.

So, two questions:
Is there a better way to search exact phrases?
Does a later release of MySQL address some of this?

Looking at the documentation for 6.0, it still looks like there's no way for me to modify the stoplist for my queries on my shared server. I have seen posts by people with better results for my first problem above, but things look bleak for the second when I read things like

Any insight on my problems would be most wonderful. Thanks in advance!

Options: ReplyQuote

Written By
Unsatisfying results for Binary Mode exact phrases
June 09, 2009 08:32PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.