innodb_ft_min_token_size = 1 performance implications

Question

If I change innodb_ft_min_token_size =1 from default of 3, will this cause a lot more disk usage? Any performance issues with search?

I want to be able to use fulltext search in 1 character in words.

Also once I make this change how would I rebuild the index? Will this put a lot of load on server?

Answer 1

There are not that many 1- and 2- letter words, so the space change may not be that great.

Modifying innodb_ft_min_token_size, innodb_ft_max_token_size, or ngram_token_size [in my.cnf] requires restarting the server.

To rebuild FULLTEXT indexes for an InnoDB table, use ALTER TABLE with the DROP INDEX and ADD INDEX options to drop and re-create each index.

-- https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html

The "Scope" of innodb_ft_min_token_size is "Global". That is, it applies to all InnoDB FT indexes.

-- https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_ft_min_token_size

Recreating the index will read the entire table and rebuild the FT index, which will "lock" the table at some level for some period of time. The time to rebuild will be roughly proportional to the size of the table. And it will consume a bunch of extra disk space until it is finished. (The table and all the indexes will be copied over and at least the FT index will be rebuilt.)

If you have a thousand rows, no big deal. If you have a billion rows, you will need a long "downtime".

After changing the innodb_ft_max_token_size, I would be afraid to do a short wildcard test like

AGAINST('a*' IN BOOLEAN MODE)

If you have a test server, simply try it.

I noticed that the documentation recommends a value of 1 for Chinese, etc.