MySQL Forums
Forum List  »  Full-Text Search

Innodb FTS problem matching phrases.
Posted by: A Fiddaman
Date: April 10, 2013 06:38PM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Innodb FTS problem matching phrases.
3848
April 10, 2013 06:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.