Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

When doing MySQL full-text search IN BOOLEAN MODE and the length of your search keyword is less than 4 characters, there won't be match although there is matching record in database. It means your search hits ft_min_word_len "minimum word length of 4" rule IN BOOLEAN MODE search modifiers. To get around of this limitation, do the following.


Dummy table


# Create table.
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `index2` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# Populate table.
INSERT INTO `person` (`name`) VALUES ('abc');

Check the current status


By default, variable ft_min_word_len is set to 4 in MySQL and the query to search "abc" won't return anything.


inanzzz@ubuntu:~$ mysql -u root -p
Enter password:

# Check 'ft_min_word_len' variable.
mysql> SHOW VARIABLES LIKE 'ft_min_word_len';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| ft_min_word_len | 4 |
+-----------------+-------+
1 row in set (0.00 sec)

# Run example query
mysql> SELECT *
-> FROM person
-> WHERE
-> MATCH(`name`) AGAINST ('abc' IN BOOLEAN MODE);
Empty set (0.00 sec)

Update ft_min_word_len variable in MySQL config


Trying to use SET GLOBAL ft_min_word_len = 3; command would produce error "#1238 - Variable 'ft_min_word_len' is a read only variable" so you should manually update MySQL config file.


inanzzz@ubuntu:~$ sudo gedit /etc/mysql/my.cnf

# Then add key-value pair in [mysqld] block as:
[mysqld]
ft_min_word_len = 3

Restart MySQL


inanzzz@ubuntu:~$ sudo service mysql restart
mysql stop/waiting
mysql start/running, process 3797

Rebuild indexes


You cannot use REPAIR command on InnoDB storage engine otherwise you get "The storage engine for the table doesn't support repair" error. You can use ALTER TABLE

ENGINE=MyISAM; to convert InnoDB to MyISAM.


mysql> REPAIR TABLE person QUICK;
+----------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+----------+
| inanzzz.person | repair | status | OK |
+----------------+--------+----------+----------+
1 row in set (0.00 sec)

Query again


mysql> SELECT *
-> FROM person
-> WHERE
-> MATCH(`name`) AGAINST ('abc' IN BOOLEAN MODE);
+----+------+
| id | name |
+----+------+
| 1 | abc |
+----+------+
1 row in set (0.00 sec)


© 2014 ‐ 2021 inanzzz.com