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 about our open source software and database management tools, please send us your question and someone will contact you.