Making sense of full text boolean mode results

Question

I've been playing around with boolean mode with MySQL's fulltext searching but I'm not understanding the results I am getting at times.

For example, here are some data rows..

auction_id,'name'
100543,'2011-12 Fleer Retro patch auto'
100544,'2011-12 Fleer Retro patch auto jordan'
100545,'2011-12 Fleer Retro autograph'
100546,'2011-12 Fleer Retro autographed'
100547,'2011-12 Fleer Retro auto'
100549,'1999 jordan auto'
100550,'1999 auto jordan'
100551,'1999 autograph jordan'
100552,'1999 autographed jordan patch'
100553,'1999 jordan non auto'

Now if I run the query:

SELECT auction_id,NAME,description FROM auctions WHERE MATCH(`name`) AGAINST('+jordan +auto' IN BOOLEAN MODE);

I get the rows 100544,100549,100550,100553 returned, which is correct. However if I run this query:

SELECT auction_id,NAME,description FROM auctions WHERE MATCH(`name`) AGAINST('+jordan +auto -non' IN BOOLEAN MODE);

I get the same results returned; shouldn't 100553 go away since it has "non" in the name?

Additionally if I change it to:

SELECT auction_id,NAME,description FROM auctions WHERE MATCH(`name`) AGAINST('+jordan +auto -"non auto"' IN BOOLEAN MODE);

I get no results returned; should I get them all except for 100553?

Answer 1

As documented under Boolean Full-Text Searches:

Boolean full-text searches have these characteristics:

[ deletia ]

• The minimum and maximum word length full-text parameters apply: innodb_ft_min_token_size and innodb_ft_max_token_size for InnoDB search indexes, and ft_min_word_len and ft_max_word_len for MyISAM ones.

As documented under Natural Language Full-Text Searches:

Some words are ignored in full-text searches:

• Any word that is too short is ignored. The default minimum length of words that are found by full-text searches is three characters for InnoDB search indexes, or four characters for MyISAM. You can control the cutoff by setting a configuration option before creating the index: innodb_ft_min_token_size configuration option for InnoDB search indexes, or ft_min_word_len for MyISAM.