Skip to content

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 for MySQL 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

innodb_ft_ignore_stopwords

Option Description
Command-line Yes
Config file Yes
Scope Global, Session
Dynamic Yes
Data type Boolean
Default OFF

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

By default, full text search is unable to find words with various punctuation characters in boolean search mode, although those characters are indexed with ngram parser. A new variable ft_query_extra_word_chars was introduced in Percona Server for MySQL Percona Server 5.7.21-20 to solve this issue.

When it’s enabled, all the non-whitespace symbols are considered to be word symbols by FTS query parser, except for the boolean search syntax symbols (which are specified by ft_boolean_syntax variable). The latter ones are also considered to be word symbols inside double quotes. This only applies for the query tokenizer, and the indexing tokenizer is not changed in any way. Because of this, the double quote symbol itself is never considered a word symbol, as no existing indexing tokenizer does so, thus searching for it would never return documents.

System Variables

ft_query_extra_word_chars

Option Description
Command-line Yes
Config file Yes
Scope Global, Session
Dynamic Yes
Data type Boolean
Default OFF

When enabled, this variable will make all non-whitespace symbols (including punctuation marks) to be treated as word symbols in full-text search queries.