InnoDB FTSでタグとかスペース区切りでID等の格納しているテーブルの検索が遅い件

よくユーザ参加型サービスを構築するとき、タグやカテゴリとか多用するのですが、大抵コロン区切りでIDをvarcharなりtextなりに格納しています。

検索はFIND_IN_SETで行いますがMySQL5.6のInnoDBではFIND_IN_SETはインデックスを使用できません。文字列型で入れてるので当たり前ですが、LIKEよりかはずっと使いやすく多用しています。

数万件程度のテーブルならそこまで問題にならないことも多いですが、10万件にもなってくるとインデックスが使用出来ない為遅さが目立ちます。

そこでInnoDB FTSでスペース区切りでIDを格納し、Full Text Searchをかけるよう試してみたら単体ではぼちぼち早い、といってもFIND_IN_SETよりはマシ程度なのですが、他のフィールドと合わせてWHERE検索するとfilesortが発生し遅くなります。

なぜfilesortが発生するかというとFull Text Searchではインデックスがその一つしか使用出来ないからです。

Full Text Search→他のWHERE句の検索のためにfilesortということになります。さらにORDERが入ってくると目も当てられません。

filesortでは十分なメモリを積んでいれば問題にならないこともありますが、大抵の場合ファイルIOが発生します。速度をそんなに必要としないシステムでもfilesortがあるとIO負荷が高まり、ローカルのSSDにmysqlバイナリを入れている場合SSDの寿命が短くなり、ネットワーク上のCIFだと帯域を消費します。

さらにFull Text Searchはインデックス作成に時間がかかり、ibdataが肥大化するというデメリットもあります。

InnoDB FTSは使いどころによってはかなり強力ですが、このような用途には向いていないようです。