MySQL Forums
Forum List  »  Newbie

Re: Using FULLTEXT index with a boolean on the matched field?
Posted by: Tom Melly
Date: September 10, 2009 12:49AM

The implication of what you're saying is that you only want to return a match when all the words in the data are matched by all the words in the search string.

e.g. if you stored War and Peace in your db, then the user would have to enter a search term that included every single word found in War and Peace in order to match against it.

This seems an odd requirement.

The only way I can think of doing it is to count the words in the entry, and see if the score of the search matches (or is greater than) the number of words.
select *,
( LENGTH(mytext) - LENGTH(REPLACE(mytext, ' ', ''))+1) as WCnt,
match(mytext) against('sweet crusty pie' in boolean mode) as score
from text_search
where match(mytext) against('sweet crusty pie' in boolean mode)
and match(mytext) against('sweet crusty pie' in boolean mode)=( LENGTH(mytext) - LENGTH(REPLACE(mytext, ' ', ''))+1)
order by score desc;

The above, for example, will match against "sweet pie" since the score is 2 and the number of words in "sweet pie" is also 2.

Edited 1 time(s). Last edit at 09/10/2009 02:44AM by Tom Melly.

Options: ReplyQuote

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.