MySQL フレーズ検索で日本語全文検索をLightに。　

MySQLのFullText Index(全文Index)を使うと、全文検索をライトにできます。

Solrや、Triton,Senner,Groongaに比べて、機能的には劣りますが、MySQLだけでできますので、
Hard,MiddleWare的にもLightだと思います。サーバー数台でやってるような小さいWebサービスで、
ちょっと全文検索いれたい！みたいな時にはぴったりです。

でも、ちょっと工夫しなくてはいけない点があります。
現時点（MySQLVer5.5）では、
FullTextIndexがサポートされているデータベースエンジンはMyISAMのみで、
スペースで区切られているような言語しかサポートされていないということ。
つまり、日本語で使用したいときは、Ngramか、形態素解析して、単語に区切らないとそのままでは使えません。

その辺のことは下記を参照してください。

MySQL を使った全文検索 (Full-Text Search)
http://www.nilab.info/zurazure2/000319.html

MySQLで全文検索 - FULLTEXTインデックスの基礎知識
http://www.tatamilab.jp/rnd/archives/000389.html#i

本家リファレンス
http://dev.mysql.com/doc/refman/5.1/ja/fulltext-search.html

MySQLのFulltext Indexを日本語対応する際の解決策は2つです。

私は、MySQL5.5を使用していて、現時点のFulltext Parser Pluginで、サポートしているものがないので、
アプリケーション側でNgram化して、MySQLのFulltext Indexを使用しています。

さらに、Boolean Modeのフレーズ検索で正しい日本語検索を行ないます。
アプリケーション側で全文検索する際は、検索クエリもNgram化しますが、その際にフレーズ検索を使用しないと、
検索してしまいます。

例えば、「ビズリーチ」をBigramした場合、「ビズ　ズリ　リー　ーチ」となり、SQL的には、下記のようなクエリとなります。

select * from table_name where match(column_name) against('+ビズ　+ズリ　+リー　+ーチ' in boolean mode);

しかし、これでは「ビズリーチ」ではないが、「ビズ　ズリ　リー　ーチ」をすべて含む文章が引っかかってしまいます。
正しく日本語検索したい場合はフレーズ検索を利用します。

select * from table_name where match(column_name) against('+"ビズ　ズリ　リー　ーチ"' in boolean mode);

こうすると、正しく「ビズリーチ」のみ検索できます。

本家リファレンス（Boolean Mode　フレーズ検索はリンクの一番下に書いてあります）
http://dev.mysql.com/doc/refman/5.1/ja/fulltext-boolean.html


また、複雑なWhere句や、ネストを含む複雑なSQLにすると、すぐにFulltextIndexを使ってくれないSQLとなり、遅くなります。
And検索の場合は、横に並べる、OR検索の場合は、Unionをします。

例えば、「(ビズリーチ and ルクサ) or はてぶ」の場合は、下記のようなSQLにすると、FulltextIndexが必ず使用されます。

select * from table_name where match(column_name) against('+"ビズ　ズリ　リー　ーチ" +"ルク クサ"' in boolean mode);
union
select * from table_name where match(column_name) against('+"はて てぶ"' in boolean mode);

なぜか、MySQLに関するエントリーが多い。次はアプリ側のことをもっと書こうかな。

MySQLのIndex Margeにはご注意を。

Index Margeとは、複数の単一インデックスをMySQLが一つのIndexに結合してくれる機能のことです。

複数のカラムにまたがってインデックスを貼る複合インデックスよりも検索速度は遅いのですが、
複合インデックスを使うと、Where句での条件指定の順番をインデックスで指定した順番通りに
しないと複合インデックスをMySQLが使ってくれなかったりします。

OR Mapperを使用して順番が指定できない場合は
複合インデックスを使いにくいケースが有り、
やはりIndex Margeを狙って、単一インデックスを複数のカラムに貼ったりします。

しかし、その場合注意しなくてはいけないのは、
どのインデックスを使ってMergeしてくれるかは、MySQLに依存するということ。

例えば、４つのカラムにインデックスを貼り、
検索時にはこの４つに対して条件を指定し
MySQLがそのうち２つを選んでIndex Margeした時、
もし、選んだ２つの条件のカーディナリティが低い場合とてもSQLが遅くなります。

MySQLの全文インデックス（FullTextーIndex）を初期移行時の注意点

MySQLのMyISAMには、全文検索（FullTextーIndex）がありますが、
初期移行時には注意が必要です。

テーブルを作成する際に、FullTextインデックスも作成すると、
初期データロード時に時間がかかり過ぎて終わりません。
おそらくデータInsert時に毎回インデックスを作り直しているからです。

FULLTEXT インデックスを作成しない状態でテーブルを作成し、大量のデータを読み込んだ後に、
インデックスを作るようにします。

そうすると、FULLTEXTインデックスの作成も数分で終わります。

大きなデータセットに関しては、FULLTEXT インデックスを持たないテーブルにロードし、
その後でインデックスを作成するほうが、すでに FULLTEXT インデックスを持つテーブルにロードするよりも断然速く読み込めます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.9 全文検索関数

反省点
MySQLのマニュアルに書いてあったのに気づかなかった。
一日以上の時間をこれに費やしてしまった。

問題が起こったときは、
どこに問題があるのかしっかり考え、原因を突き止めてから次の手を考える。
問題の原因を突き止めるときは、大きい部分から徐々に小さいところへ詰めていくように気をつけよう。

闇雲にいろんな手を試したりしてると、問題の原因がどこなのかわからなくなる。