MySQL full-text search diffirences between IN BOOLEAN MODE and IN NATURAL LANGUAGE MODE

I prefer using IN BOOLEAN MODE modifier with InnoDB storage engine. Modifier IN BOOLEAN MODE has less limitations and InnoDB storage engine is more powerful in general. No matter what storage engine is used, 50% threshold rule doesn't apply to IN BOOLEAN MODE modifier at all. InnoDB automatically orders results by relevance in full-text search.

Facts

• A full-text index in MySQL is an index of type FULLTEXT.


• Full-text indexes can be created only for CHAR, VARCHAR or TEXT columns.


• nnoDB and MyISAM tables use FULLTEXT indexes.


• InnoDB storage engine requires MySQL version 5.6 as minimum.


• When doing batch data import, remove FULLTEXT index to speed up the process and add it to table afterwards with ALTER TABLE or CREATE INDEX.


• The "minimum word length of 3" rule applies to InnoDB storage engine.


• The "minimum word length of 4" rule applies to MyISAM storage engine.


• Stop words such as 'on', 'the' or 'it' are ignored during searching.


• Modifier IN NATURAL LANGUAGE MODE automatically orders results by relevance.


• Modifier IN BOOLEAN MODE doesn't order results automatically.


• For InnoDB storage engine, innodb_ft_min_token_size and innodb_ft_max_token_size variables are used to control minimum and maximum word length limits.


• For MyISAM storage engine, ft_min_word_len and ft_max_word_len variables are used to control minimum and maximum word length limits.


• No matter what storage engine is used, 50% threshold rule doesn't apply to IN BOOLEAN MODE modifier.

Test data within a table

mysql> SELECT * FROM person;
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | john |         |
|  2 |      | john    |
|  3 | a    |         |
|  4 |      | b       |
|  5 | c    | c       |
|  6 | john | john    |
+----+------+---------+
6 rows in set (0.00 sec)

IN NATURAL LANGUAGE MODE modifier test

Storage engine is MyISAM. No match because 'john' present in 3 rows which is more than 50% of all rows. 50% threshold rule applies to only MyISAM storage engine. To make it work, you'll have to add a few more records without 'john' in them.

mysql> SELECT *
    -> FROM person
    -> WHERE
    -> MATCH(`name`, `surname`) AGAINST ('john' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)

Storage engine is InnoDB. Match found because 50% threshold rule doesn't apply to InnoDB storage engine. Results are automatically ordered by relevance.

mysql> SELECT *
    -> FROM person
    -> WHERE
    -> MATCH(`name`, `surname`) AGAINST ('john' IN NATURAL LANGUAGE MODE);
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  6 | john | john    |
|  1 | john |         |
|  2 |      | john    |
+----+------+---------+
3 rows in set (0.00 sec)

IN BOOLEAN MODE modifier test

Storage engine is MyISAM. Match found because 50% threshold rule doesn't apply to IN BOOLEAN MODE modifier. Results are not automatically ordered by relevance.

mysql> SELECT *
    -> FROM person
    -> WHERE
    -> MATCH(`name`, `surname`) AGAINST ('john' IN BOOLEAN MODE);
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  1 | john |         |
|  2 |      | john    |
|  6 | john | john    |
+----+------+---------+
3 rows in set (0.00 sec)

Storage engine is InnoDB. Match found because 50% threshold rule doesn't apply to IN BOOLEAN MODE modifier. Results are automatically ordered by relevance.

mysql> SELECT *
    -> FROM person
    -> WHERE
    -> MATCH(`name`, `surname`) AGAINST ('john' IN BOOLEAN MODE);
+----+------+---------+
| id | name | surname |
+----+------+---------+
|  6 | john | john    |
|  1 | john |         |
|  2 |      | john    |
+----+------+---------+
3 rows in set (0.00 sec)