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.

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.

  • 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) Jun 10, 2011 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 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 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 !!!

  • 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, 2011 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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