InnoDB FTSで検索できない文字があるときは innodb_ft_enable_stopword のせいかも
InnoDB FTS シリーズが続きます。今回は innodb_ft_enable_stopword についてです。
TL; DR
- ngram で、yakitori がヒットしない
mysql> SELECT * FROM fts_ngram;
+----+----------+
| id | c1 |
+----+----------+
| 1 | yakitori |
+----+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM fts_ngram WHERE MATCH(`c1`)
> AGAINST('tori' IN BOOLEAN MODE);
Empty set (0.00 sec)
innodb_ft_enable_stopwordが ON が原因innodb_ft_enable_stopwordは デフォルトでON
- LIKE検索のような単純な文字列マッチを行う場合は、
innodb_ft_enable_stopword = OFFにすべき
ストップワードとは何か
ストップワードは検索インデックスに登録されません。 MySQLでは、デフォルトのストップワードとして、このように英語の文章で頻出する単語があらかじめ登録されています。 人間は、（固有名詞など）特徴のあるキーワードで検索をします。以下のような頻出語を検索することはないため、インデックス化の対象外となっているわけです。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD; +-------+ | value | +-------+ | a | | about | | an | | are | | as | | at | | be | | by | | com | | de | | en | | for | | from | | how | | i | | in | | is | | it | | la | | of | | on | | or | | that | | the | | this | | to | | was | | what | | when | | where | | who | | will | | with | | und | | the | | www | +-------+ 36 rows in set (0.00 sec)
実際に試してみます。There is an apple という文章に対して、FULLTEXTインデックスを作成します。
デフォルトのトークナイザはスペース区切りでトークナイズします。この場合は、there is an apple の４つにトークナイズされることになります。
しかし、インデックスに保存されたワードは apple と there のみになっています。
is と an は ストップワードに マッチ（完全一致）したため、無視されています。
mysql> CREATE TABLE `fts_defparser` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
mysql> INSERT INTO fts_defparser (c1) VALUES ('There is an apple');
mysql> SET GLOBAL innodb_ft_aux_table='t/fts_defparser';
mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;
+-------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-------+--------------+-------------+-----------+--------+----------+
| apple | 2 | 2 | 1 | 2 | 12 |
| there | 2 | 2 | 1 | 2 | 0 |
+-------+--------------+-------------+-----------+--------+----------+
2 rows in set (0.01 sec)
apple は検索できますが、
mysql> SELECT * FROM fts_defparser WHERE MATCH(`c1`) AGAINST('apple' IN BOOLEAN MODE);
+----+-------------------+
| id | c1 |
+----+-------------------+
| 2 | There is an apple |
+----+-------------------+
1 row in set (0.00 sec)
an はインデックス化されなかったため、検索することができません。
mysql> SELECT * FROM fts_defparser WHERE MATCH(`c1`) AGAINST('an' IN BOOLEAN MODE);
Empty set (0.00 sec)
ngram の場合の挙動
ngram の場合の挙動は MySQL Server Team のブログ に以下のような記述があります。 要するにトークナイズした結果が、ストップワードに部分一致すると（ngramでなければ、完全一致でした）、そのワードはインデックスに登録されないと。
ストップワードの処理： stopwordsの処理も少し異なります。通常、トークン化された単語自体(完全一致)がストップワードテーブルにあるならば、その単語には全文検索インデックスは作成されません。しかしながら、n-gramパーサーの場合は、トークン化された単語がストップワードを含んでいないか確認し、含んでいればインデックスを作成しません。このように動作が異なる理由は、私たちのCJKは非常に多くの頻繁に使われる無意味な文字、単語、句読点を持っているからです。ストップワードに一致する文字が含まれているかを確認する方式を使うと、より役に立たないトークンを除去できます。
yakitori は ya ak ki it to or ri にトークナイズされます。
it to or はストップワードに完全一致していますが、ya ak ki ri もa や i に部分一致しているため、無視されてしまいました。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD WHERE VALUE IN ('a', 'it', 'to', 'i', 'or');
+-------+
| value |
+-------+
| a |
| i |
| it |
| or |
| to |
+-------+
5 rows in set (0.00 sec)
では、innodb_ft_enable_stopword をOFFにして確認します。
mysql> SET innodb_ft_enable_stopword = OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> OPTIMIZE TABLE fts_ngram;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+----------+----------+-------------------------------------------------------------------+
| t.fts_ngram | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| t.fts_ngram | optimize | status | OK |
+-------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.23 sec)
mysql> SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE ORDER BY POSITION;
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| ya | 2 | 2 | 1 | 2 | 0 |
| ak | 2 | 2 | 1 | 2 | 1 |
| ki | 2 | 2 | 1 | 2 | 2 |
| it | 2 | 2 | 1 | 2 | 3 |
| to | 2 | 2 | 1 | 2 | 4 |
| or | 2 | 2 | 1 | 2 | 5 |
| ri | 2 | 2 | 1 | 2 | 6 |
+------+--------------+-------------+-----------+--------+----------+
7 rows in set (0.01 sec)
mysql> SELECT * FROM fts_ngram WHERE MATCH(`c1`) AGAINST('tori' IN BOOLEAN MODE);
+----+----------+
| id | c1 |
+----+----------+
| 1 | yakitori |
+----+----------+
1 row in set (0.00 sec)
yakitoriが検索できるようになりました！
