Buy Percona ServicesBuy Now!

InnoDB Full-Text Search improvements

Ignoring Stopword list

By default all Full-Text Search indexes check the stopwords list, to see if any indexed elements contain one of the words on that list.

Using this list for n-gram indexes isn’t always suitable, as an example, any item that contains a or i will be ignored. Another word that can’t be searched is east, this one will find no matches because a is on the FTS stopword list.

To resolve this issue, in Percona Server 5.7.20-18 a new innodb_ft_ignore_stopwords variable has been implemented which controls whether InnoDB Full-Text Search should ignore stopword list.

Although this variable is introduced to resolve n-gram issues, it affects all Full-Text Search indexes as well.

Being a stopword doesn’t just mean to be a one of the predefined words from the list. Tokens shorter than innodb_ft_min_token_size or longer than innodb_ft_max_token_size are also considered stopwords. Therefore, when innodb_ft_ignore_stopwords is set to ON even for non-ngram FTS, innodb_ft_min_token_size / innodb_ft_max_token_size will be ignored meaning that in this case very short and very long words will also be indexed.

System Variables

variable innodb_ft_ignore_stopwords
Command Line:Yes
Config File:Yes
Scope:Session, Global
Dynamic:Yes
Variable Type:Boolean
Default Value:OFF

When enabled, this variable will instruct InnoDB Full Text Search parser to ignore the stopword list when building/updating an FTS index.

Visit Percona Store


General Inquiries

For general inquiries, please send us your question and someone will contact you.