Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to come up with a mysql boolean search that matches "monkey" but ignores / doesn't take into account any matches of "monkey business".

Something like the following is the general idea but it negates the value. I need it to not match rows with "monkey business" unless they also have "monkey" without "business" after it elsewhere in the text.

SELECT * FROM table
MATCH (title) against ('+monkey ~"monkey business"' IN BOOLEAN MODE);

Is it possible? Thanks in advance for any help you can give! I realise this could be done with better search engines, at the moment fulltext is all I can use.

share|improve this question
    
I haven't tried, nor can I test it out atm. But did you try switching the order? ...AGAINST( '-"monkey business" +monkey' IN BOOLEAN MODE) –  Derek Downey Jun 10 '11 at 14:18

1 Answer 1

I tend to be a little conservative and cautious when using FULLTEXT indexes because FULLTEXT indexes has a nasty habit of playing head games with the MySQL Query Optimizer.

Try getiing all monkey rows first in a subquery, and then eliminate "monkey business":

SELECT * FROM
(
    SELECT id,title FROM table
    WHERE MATCH (title) against ('+monkey' IN BOOLEAN MODE)
) A
WHERE LOCATE('monkey business',title) = 0;

You could also do the reverse by getting all rows that do not have "monkey business" and then scan for any rows with monkey:

SELECT * FROM
(
    SELECT id,title FROM table
    WHERE MATCH (title) against ('-"monkey business"' IN BOOLEAN MODE)
) A
WHERE LOCATE('monkey',title) > 0;

The second query would make the temp table from the subquery much larger and would take longer to scan for the outside WHERE clause. Go with the first query.

Give it a Try !!!

share|improve this answer
    
Thanks for your response! However that isn't quite what I'm trying to do. Essentially I'm trying to create a negative lookahead with SQL. In Regex it would be something like '/monkey\s(!?business)/'. –  Matt Jun 14 '11 at 7:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.