あなたのMySQLは「KING」を全文検索できますか？

この記事は

MySQLで全文検索を設定していたら地獄のような仕様を踏んだのでメモです。

やりたいこと

• 単にMySQLでngramの全文検索を実施したかったです

事象

• 全文検索時に「一部の」単語が引っかからない
• 例えば、「QUEEN」は引っかかるけど、「KING」は引っかからない

原因

• MySQLではデフォルトでストップワードという、全文検索に引っかからなくなるワードが定義されていて、下記の単語が含まれています

mysqlのソース

const char *fts_default_stopword[] =
{
    "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",
    NULL
};

• このリスト内にある「in」にぶつかって、「KING」の場合だけ検索結果に含まれなくなっていたのでした。
• バイグラムでkingはki in ngに分割されるので真ん中のgramが一致してしまってるわけです。
• 英語の場合はngramじゃなくてスペースで分かち書きすればいいので問題にならないのでしょうが。。

対処

• 作業前にこの機能がONになっていることを確認して、これが原因であることを確定させておきます。

mysql> SHOW VARIABLES LIKE 'innodb_ft_enable_stopword';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_ft_enable_stopword | ON    |
+---------------------------+-------+
1 row in set (0.01 sec)

• こんな機能は不要なのでmy.cnfとかRDSのパラメータグループでオフってしまいましょう

my.cnf

innodb_ft_enable_stopword=0

• 反映させるためにmysqlを再起動します
• 念のためパラメータが落ちていることを確認します

mysql> SHOW VARIABLES LIKE 'innodb_ft_enable_stopword';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_ft_enable_stopword | OFF   |
+---------------------------+-------+
1 row in set (0.01 sec)

• すでに全文検索インデックスが作られているテーブルに手動で反映します

mysql> OPTIMIZE TABLE table1;

結果

• KINGが引っかかるようになりました！

終わりに

• これ気づかないと気づかないから、世の中でそのままになってるサーバたくさんありそう・・・

参考

• MySQL InnoDBのフルテキストインデックスで、ストップワードフィルタを無効にする (in, by, is などで検索できるようにする)