sql MATCH AGAINST BOOLEAN MODE returning 0 results

Question

This is the query:

SELECT title from prod_table WHERE MATCH(title) AGAINST ('-Gears Of War' IN BOOLEAN MODE) limit 30;

The table is definitely populated and here is schema

SQL query: explain prod_table;
Rows: 22
Field           Type        Null        Key     Default     Extra
PRprodinfo_id   int(11)     NO          PRI     NULL    auto_increment
PRid            varchar(64) NO  UNI     NULL    
main_category   varchar(64) YES         NULL    
title           varchar(128)NO  MUL     NULL    
agegroup        varchar(16) YES         NULL    
author          varchar(128)YES         NULL    
sex             varchar(8)  YES         NULL    
actors          varchar(256)YES         NULL    
platform        varchar(64) YES         NULL    
artist          varchar(128)YES         NULL    
genre           varchar(128)YES         NULL    
description     text        YES         NULL    
manuf           varchar(128)YES         NULL    
manufID         varchar(64) YES         NULL    
prodcat         varchar(128)YES         NULL    
prodcatID       int(11)     YES         NULL    
userrating      decimal(1,1)NO      NULL    
profrating      decimal(1,1)NO      NULL    
lowprice        decimal(10,2)   NO      NULL    
highprice       decimal(10,2)   NO      NULL    
imageURL        varchar(128)    NO      NULL    
dateadded       timestamp       NO      CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Also tried:

SELECT title from PRprodINFO WHERE MATCH(title) AGAINST ("-'Gears Of War'" IN BOOLEAN MODE) limit 30;

and

SELECT title from PRprodINFO WHERE MATCH(title) AGAINST ("'-Gears Of War'" IN BOOLEAN MODE) limit 30;

but they kind of cancel themselves out, some of the reults are:

'Gears and Gear Cutting Workshop Practice'
'Gears of War 2 Signature Series Guide Bradygames Signature Guides'
'Gears of War 2 Last Stand Edition Guide Bradygames Signature'
'Cream  Disraeli Gears  Classic Albums DVD RETAIL BUT NOT AVAILABLE TO RENT'
'Gears of War 2 Limited Edition'
'Gears Of War'
'Gears of War 2'
'Gears of War Triple Pack'
'Gears of War 2 Game of the Year Edition'
'Gears of War 3 Limited Edition'
'Gears of War 3'

Which is exactly what i DON'T want

I'm stumped - also, on the title field there is a FULLTEXT index.

Thanks

Darren

Answer 1

Using a single negation query isn't supported. Read the documentation. It states:

Note: The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by - returns an empty result. It does not return “all rows except those containing any of the excluded terms.”

So this is not possible. You must include something else for the query to find, to exclude the rows matched with the -.

Just my opinion here, but I think it would be way too intensive to try to return a list of all rows that doesn't match some arbitrary term. The best solution would be to use a "like" query as follows.

SELECT title from prod_table 
WHERE NOT title LIKE '%Gears Of War%' 
limit 30;

Answer 2

Try this:

SELECT title from prod_table WHERE MATCH(title) AGAINST ( "-'Gears Of War'" IN BOOLEAN MODE) limit 30;

with single quotes around the words.

Answer 3

You won't be able to solve this problem using MATCH AGAINST operator. A solution would be to negate a LIKE query. E.g:

SELECT title FROM prod_table WHERE title NOT LIKE '%Gears Of War%' LIMIT 30;