gistfile1.txt

DROP DATABASE IF EXISTS mroonga;
CREATE DATABASE mroonga CHARSET utf8mb4;
USE mroonga
CREATE TABLE mroonga.t1 (num int primary key, val varchar(32),
                         fulltext key bigram  (val) Comment 'parser "TokenBigram"',
                         fulltext key trigram (val) Comment 'parser "TokenTrigram"'
) Engine = Mroonga;

INSERT INTO mroonga.t1 VALUES (1, 'ザクとおでかけ'), (2, '水陸両用ザクcoming soon'), (3, '秋冬兼用ザク入荷'), (4, 'ザク');

SET mroonga_match_escalation_threshold= -1;
SELECT * FROM mroonga.t1;
SELECT * FROM mroonga.t1 USE INDEX (bigram) WHERE match(val) against ('*D+ ザク' IN BOOLEAN MODE);
SELECT * FROM mroonga.t1 USE INDEX (trigram) WHERE match(val) against ('*D+ ザク' IN BOOLEAN MODE);
SELECT * FROM mroonga.t1 USE INDEX (bigram) WHERE match(val) against ('*D+ ザ' IN BOOLEAN MODE);
SELECT * FROM mroonga.t1 USE INDEX (trigram) WHERE match(val) against ('*D+ ザ' IN BOOLEAN MODE);
SELECT * FROM mroonga.t1 USE INDEX (bigram) WHERE match(val) against ('*D+ ザク*' IN BOOLEAN MODE);
SELECT * FROM mroonga.t1 USE INDEX (trigram) WHERE match(val) against ('*D+ ザク*' IN BOOLEAN MODE);

gistfile2.md

mysql> SELECT * FROM mroonga.t1;
+-----+-------------------------------+
| num | val                           |
+-----+-------------------------------+
|   1 | ザクとおでかけ                |
|   2 | 水陸両用ザクcoming soon       |
|   3 | 秋冬兼用ザク入荷              |
|   4 | ザク                          |
+-----+-------------------------------+
4 rows in set (0.00 sec)

• こんなデータが入っていて

mysql> SELECT * FROM mroonga.t1 USE INDEX (trigram) WHERE match(val) against ('*D+ ザク' IN BOOLEAN MODE);
+-----+-------------------------------+
| num | val                           |
+-----+-------------------------------+
|   2 | 水陸両用ザクcoming soon       |
|   4 | ザク                          |
+-----+-------------------------------+
2 rows in set (0.00 sec)

• "*ザク"で終わっているトークンだけがマッチする。

mysql> SELECT * FROM mroonga.t1 USE INDEX (trigram) WHERE match(val) against ('*D+ ザ' IN BOOLEAN MODE);
+-----+-------------------------------+
| num | val                           |
+-----+-------------------------------+
|   1 | ザクとおでかけ                |
|   2 | 水陸両用ザクcoming soon       |
|   3 | 秋冬兼用ザク入荷              |
|   4 | ザク                          |
+-----+-------------------------------+
4 rows in set (0.01 sec)

• 1文字の場合は前方一致検索に書き換えてくれているぽい。

mysql> SELECT * FROM mroonga.t1 USE INDEX (trigram) WHERE match(val) against ('*D+ ザク*' IN BOOLEAN MODE);
+-----+-------------------------------+
| num | val                           |
+-----+-------------------------------+
|   2 | 水陸両用ザクcoming soon       |
+-----+-------------------------------+
1 row in set (0.01 sec)

• 1の"ザクとおでかけ" はマッチしてくれない？

gistfile3.txt

mysql> SHOW VARIABLES LIKE 'mroonga%';
+----------------------------------------+----------------------+
| Variable_name                          | Value                |
+----------------------------------------+----------------------+
| mroonga_action_on_fulltext_query_error | ERROR_AND_LOG        |
| mroonga_boolean_mode_syntax_flags      | DEFAULT              |
| mroonga_database_path_prefix           |                      |
| mroonga_default_parser                 | TokenBigram          |
| mroonga_default_wrapper_engine         |                      |
| mroonga_dry_write                      | OFF                  |
| mroonga_enable_optimization            | ON                   |
| mroonga_libgroonga_support_lz4         | OFF                  |
| mroonga_libgroonga_support_zlib        | ON                   |
| mroonga_libgroonga_version             | 5.0.3                |
| mroonga_lock_timeout                   | 10000000             |
| mroonga_log_file                       | groonga.log          |
| mroonga_log_level                      | NOTICE               |
| mroonga_match_escalation_threshold     | 18446744073709551615 |
| mroonga_max_n_records_for_estimate     | 1000                 |
| mroonga_vector_column_delimiter        |                      |
| mroonga_version                        | 5.02                 |
+----------------------------------------+----------------------+
17 rows in set (0.00 sec)