MySql FullTextSearch in Boolean mode returning returning results shorter than ft_min_word_len

Question

I am using Mysql FTS query as below in Boolean Mode

SELECT org.org_name FROM org 
WHERE MATCH org.org_name AGAINST ('an*' in BOOLEAN MODE);

and this gives me the following result

+--------------------------+
| org_name                 |
+--------------------------+
| Chairs and Table Company |
| and                      |
+--------------------------+
2 rows in set (0.00 sec)

However, my understanding is that all results less than ft_min_word_len are ignored and in my case, ft_min_word_len is 4 as shown in the below results from this query (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)     |
+--------------------------+----------------+

I am wondering why the results like 'and' are being returned if my understanding is correct. Thanks.

Is your table using InnoDB? The parameters you show here relate to MyISAM tables. See this. https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_min_token_size — O. Jones, Jun 16 '18 at 13:06
I am using InnoDB - checked using SHOW TABLE STATUS WHERE `Name` = 'org'; — HopeKing, Jun 16 '18 at 13:16
ok got it - the innodb_ft_min_token_size is 3. If i added another value 'an' in the db, that doesn't come up !! Thanks - if you put that as an answer i could accept it. — HopeKing, Jun 16 '18 at 13:19

score 2 · Accepted Answer · answered Jun 16 '18 at 17:45

FULLTEXT searching is available in both InnoDB and MyISAM tables. But, they have different sets of variables controlling them, for example.

MyISAM                   InnoDB
ft_min_word_len     innodb_ft_min_token_size
ft_max_word_len     innodb_ft_max_token_size

SHOW TABLE STATUS WHERE Name = 'tablename' shows you the table's engine.

This may be helpful reading.

MySql FullTextSearch in Boolean mode returning returning results shorter than ft_min_word_len

1 Answers1