inanzzz | MySQL full-text search IN BOOLEAN MODE minimum word length limitation

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)