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.

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



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)