MySQL 5.6 InnoDB FTSのストップワードテーブルを設定する

ストップワードはつまり "この単語は無視する" リストであり、転置索引に登録されなくなる。MyISAM FTSはファイルで入れないといけなかった(ft_stopword_file)が、InnoDB FTSはテーブルとして設定する(innodb_ft_server_stopword_table .. インスタンス全体で共通に使うストップワードテーブル と innodb_ft_user_stopword_table .. 使い分けられるんだけどちょっともんにょりするストップワードテーブル)

innodb_ft_user_stopword_tableのどこがどうもんにょりするかはちょっと置いておいて、単純明快なinnodb_ft_server_stopword_tableの方を使って実験する。転置索引は 前の記事 で作った、自力で分かち書きした5.6.23のInnoDB FTインデックス。

mysql56> SELECT COUNT(*) FROM information_schema.INNODB_FT_INDEX_TABLE;
+----------+
| COUNT(*) |
+----------+
|   453957 |
+----------+
1 row in set (1.41 sec)

mysql56> SELECT COUNT(DISTINCT word) FROM information_schema.INNODB_FT_INDEX_TABLE;
+----------------------+
| COUNT(DISTINCT word) |
+----------------------+
|                29562 |
+----------------------+
1 row in set (2.85 sec)

mysql56> SELECT COUNT(DISTINCT doc_id) FROM information_schema.INNODB_FT_INDEX_TABLE;
+------------------------+
| COUNT(DISTINCT doc_id) |
+------------------------+
|                  23470 |
+------------------------+
1 row in set (1.94 sec)

mysql56> SELECT COUNT(DISTINCT doc_id) FROM information_schema.INNODB_FT_INDEX_TABLE WHERE word= 'MySQL';
+------------------------+
| COUNT(DISTINCT doc_id) |
+------------------------+
|                   4464 |
+------------------------+
1 row in set (3.06 sec)

mysql56> SELECT COUNT(*) FROM d1.tweets WHERE match(fts) against ('MySQL');
+----------+
| COUNT(*) |
+----------+
|     4464 |
+----------+
1 row in set (0.36 sec)

ストップワードを何も設定せずに作った転置索引の中に入っているトークンは全部で453,957個、重複を除いた単語の数は29,562単語、総文書数は23,470文書でそのうち"MySQL"という単語に紐付けられた文書は4,464文書だ。というわけでWHERE match(fts) against ('MySQL')で取ると4,464件返ってくる。

ではストップワードテーブルを作る。

mysql56> CREATE TABLE stopwords (value varchar(255) NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.07 sec)

mysql56> INSERT INTO stopwords VALUES ('MySQL');
Query OK, 1 row affected (0.02 sec)

mysql56> SET GLOBAL innodb_ft_server_stopword_table = 'd1/stopwords';
Query OK, 0 rows affected (0.00 sec)

テーブルの名前は何でもいい。カラムの名前は"value"固定で、データ型はvarcharでないといけないらしい。PRIMARY KEYにしなくても動くには動いた。
ストップワードを指定したら、FTインデックスを作り直す。

mysql56> ALTER TABLE tweets DROP KEY fts;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> ALTER TABLE tweets ADD FULLTEXT KEY (fts);
Query OK, 0 rows affected (3.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> SELECT COUNT(*) FROM information_schema.INNODB_FT_INDEX_TABLE;
+----------+
| COUNT(*) |
+----------+
|   451915 |
+----------+
1 row in set (1.44 sec)

mysql56> SELECT COUNT(DISTINCT word) FROM information_schema.INNODB_FT_INDEX_TABLE;
+----------------------+
| COUNT(DISTINCT word) |
+----------------------+
|                29595 |
+----------------------+
1 row in set (2.74 sec)

mysql56> SELECT COUNT(DISTINCT doc_id) FROM information_schema.INNODB_FT_INDEX_TABLE;
+------------------------+
| COUNT(DISTINCT doc_id) |
+------------------------+
|                  23470 |
+------------------------+
1 row in set (1.94 sec)

mysql56> SELECT COUNT(DISTINCT doc_id) FROM information_schema.INNODB_FT_INDEX_TABLE WHERE word= 'MySQL';
+------------------------+
| COUNT(DISTINCT doc_id) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (1.43 sec)

mysql56> SELECT COUNT(*) FROM d1.tweets WHERE match(fts) against ('MySQL');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

あれワード増えてる…information_schemaだから統計情報上の誤差なのか、それともストップワード判定したから単語の切り方が変わったか。他はまあまあ想定した通りに変わっている(気がする)

mysql56> SELECT word, COUNT(*) FROM information_schema.INNODB_FT_INDEX_TABLE GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
+------+----------+
| word | COUNT(*) |
+------+----------+
| の   |    16573 |
| て   |    11059 |
| に   |    11036 |
| が   |    10630 |
| た   |     9673 |
| は   |     8460 |
| _    |     7707 |
| で   |     7624 |
| を   |     6804 |
| と   |     6432 |
+------+----------+
10 rows in set (5.43 sec)

mysql56> INSERT INTO stopwords SELECT word FROM information_schema.INNODB_FT_INDEX_TABLE GROUP BY 1 HAVING COUNT(*) > 1000;
Query OK, 54 rows affected (5.41 sec)
Records: 54  Duplicates: 0  Warnings: 0

mysql56> ALTER TABLE tweets DROP KEY fts;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> ALTER TABLE tweets ADD FULLTEXT KEY (fts);
Query OK, 0 rows affected (1.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> SELECT COUNT(*) FROM information_schema.INNODB_FT_INDEX_TABLE;
+----------+
| COUNT(*) |
+----------+
|   256196 |
+----------+
1 row in set (0.52 sec)

というわけでINNODB_FT_INDEX_TABLEからいかにもな感じのする頻出単語を掴んでごりっとINSERTしてやると、ノイズが減らせたりサイズがコンパクトになったりするかも知れない。