Innodb FTS problem matching phrases.

[A Fiddaman]

Hi all,

I'm in the process of migrating an application from MyISAM FTS to InnoDB FTS and I cannot get boolean searches for phrases to work at all. Can anyone see what I'm doing wrong? Thanks in advance!

MySQL version 5.6.10


MyISAM table:

CREATE TABLE `epg` (
`service_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`event_id` bigint(20) unsigned NOT NULL DEFAULT '0',
...
`name` varchar(255) DEFAULT NULL,
`text` varchar(255) DEFAULT NULL,
...
PRIMARY KEY (`service_id`,`event_id`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `text` (`text`),
FULLTEXT KEY `name_2` (`name`,`text`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> select name, match(name) against('+"House Doctor"' in boolean mode) as score from epg where name = 'House Doctor';
+--------------+-------+
| name | score |
+--------------+-------+
| House Doctor | 1 |
| House Doctor | 1 |
| House Doctor | 1 |
| House Doctor | 1 |
| House Doctor | 1 |
...


Whereas with an InnoDB table, same structure, same data, same query:

+--------------+-------+
| name | score |
+--------------+-------+
| House Doctor | 0 |
| House Doctor | 0 |
| House Doctor | 0 |
| House Doctor | 0 |
| House Doctor | 0 |

If I drop the 'in boolean mode', then:

+--------------+--------------------+
| name | score |
+--------------+--------------------+
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |

and trying against('+House +Doctor' in boolean mode):

+--------------+--------------------+
| name | score |
+--------------+--------------------+
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |
| House Doctor | 11.792352676391602 |

as expected, so I can't see why the +"House Doctor" phrase isn't working.


I've tried disabling the stopword list but it hasn't made any difference.


mysql> show variables like '%ft%';
+---------------------------------+----------------+
| Variable_name | Value |
+---------------------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | OFF |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 2 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_user_stopword_table | |
+---------------------------------+----------------+
15 rows in set (0.00 sec)

Thanks!