Comments - Full-Text Index Overview

innodb_ft_min_token_size defaults to 3, as per https://mariadb.com/kb/en/innodb-system-variables/#innodb_ft_min_token_size , contrary to the "Excluded Results" paragraph. (There may be other inconsistencies between MyISAM and InnoDB, too.)

Thanks, fixed.

would be nice if min_word_length / max_word_length were settable per index rather then globally. For example, a table like CREATE TABLE user_agreement (... full_name TEXT, agreement TEXT... ) . I think, for agreement field index with skipped words of < 3 character could be OK, thus for names like 'Obi van Genobi ' - not sure.

I have a table called memname_AU (name varchar(100)) with two values:

| WEST STREET |
| WEST PYENGANA ROAD |

The following seems to be a bug: (it does not match the full input)

MariaDB [geocoder]> SELECT name FROM memname_AU WHERE MATCH(name) AGAINST('West PYENGANA') order by cctn desc limit 1;
+-------------+
| name        |
+-------------+
| WEST STREET |
+-------------+
1 row in set (0.026 sec)

MariaDB [geocoder]> SELECT name FROM memname_AU WHERE MATCH(name) AGAINST('PYENGANA') order by cctn desc limit 1;
+--------------------+
| name               |
+--------------------+
| WEST PYENGANA ROAD |
+--------------------+
1 row in set (0.001 sec)

This doesn't appear to be a bug, as your first query matches against "West" as well, and you specify LIMIT 1, so only the one result is returned. (The space is stripped out, so the search is for the word "West", or the word "PYENGANA").

If you want both words to be present, you can use something like:

SELECT name FROM memname_AU WHERE MATCH(name) AGAINST('+West +PYENGANA' IN BOOLEAN MODE);

It seems that MATCH(column) AGAINST('"word1 word2"') will display rows that contain "word1-word2" (with hyphen instead of space) in the text, even when the text doesn't contain exact phrase "word1 word2". I'm not sure it this should be the case...

Articles says that there are no special operators IN NATURAL LANGUAGE MODE. But the results of AGAINST ('"word1 word2"') and AGAINST ('word1 word2') differ so that the double quotes seem to have the same meaning as IN BOOLEAN MODE, i.e. they mean a phrase. Also, the article doesn't mention a space and a dot as delimiters. Besides that, there seems to be a typo in the article phrase "consist of one OF more" (should be "OR").

I see MySQL 5.7 support ngram full-text parser for Innodb. But I can't find it on my localhost server ( installed MariaDB 10.1.8 ). Anyone knows how to use ngram full-text parser? I need it for my dictionary site http://dict.site to do Chinese search.

This is not available on MariaDB. I suggest submitting a task request on https://mariadb.org/jira

The page says: "Full-text indexes can be used only with MyISAM tables". But InnoDB supports fulltext in 10.0.

And of course also Aria supports fulltext.

Thanks - this article was moved from an old article and will be revamped soon. I've added the mention of Aria. InnoDB is not currently supported, but will be soon - see https://mariadb.atlassian.net/browse/MDEV-4744