Index on character column in Azure DB for MYSQL servers

Question

We are using Azure DB for MYSQL Flexible servers. Its InnoDB v8. In one of the table few million records exist. Sometimes we need to search record based on one of the column say Name column. Its varchar(120). It contains values like A Jr White, Mark Wilson, William Jeff Biden etc.. If we try to search Jr, ist example record should be found.

We checked Prefix indexes, Partial indexes but seems Full text should solve our problem. So we implemented full-text index.

However when we search for *Jr * (Star Jr Star without blank spaces)ist record does not return in result.

Select Name from MyTable where match(Name) AGAINST ("* Jr *" in boolean mode)

Got some suggestion to create reverseName column and store reverse of Name in this column and run on both Name or reverseName columns.

We are NOT getting the results.

Suggest please.

Answer 1

As @Georg Richter said

InnoDB full-text search indexes by default do not include terms with lengths of less than 3 characters or more than 84 characters. The innodb_ft_max_token_size and innodb_ft_min_token_size variables allow for the configuration of word length values at the maximum and minimum levels.

To resolve this issue you need to edit the server parameter innodb_ft_min_token_size to 1 since the default is 3. You can do it using Portal.

then again login your database and try you query.