Mysql fulltext boolean ignore phrase

Question

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.

• Derek Downey: 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)

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 !!!

• Matt: 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)/'.