ＭｙＳＱＬの全文検索

自作のサービスがレコード件数に比例して検索時間がかかるようになったので改善したい。

ＭｙＳＱＬを５．５から５．７にバージョンアップしました。 ５．５は英語でＭｙＩＳＡＭだけ、５．６は英語でＩｎｎｏＤＢもＯＫ、５．７でようやく日本語をサポートしました。 「ＭｙＩＳＡＭだけ」とか「ＭｅＣａｂのインストールが必要」といった古いブログがあるので記事の投稿年をよく見ましょう。 ５．７ならＭｅＣａｂも同梱してあるので手軽に使えます。

こちらのスライドが丁寧に説明されていて分かりやすかったです。 ｎ－ｇｒａｍとＭｅＣａｂの二択なのでＭｅＣａｂを選択しました。

• MySQL 5.7 InnoDB 日本語全文検索
• MySQL 5.7 InnoDB 日本語全文検索（その２）
• MySQL 5.7 InnoDB 日本語全文検索（その3）

よく見るとオラクルはＭｙＳＱＬの開発に参加してるトコだった。
MySQL :: MySQL 5.6 リファレンスマニュアル←日本語版は５．６
スライドに載っていないこと

• my.cnfにloose-mecab-rc-fileを追加した後の再起動で警告が出る（その後の作業で解消）
• my.cnfに書く変数名の語の間がハイフン「-」とアンダースコア「_」の変数で混在（どちらも可）
• ＭｅＣａｂを辞書と呼んでいる（形態素解析エンジンのはず→MeCab: Yet Another Part-of-Speech and Morphological Analyzer）

こちらのブログを参考にインデックスを追加します。
FULLTEXT INDEXは一個ずつ作ろう(by MySQL5.7) - 41から始めました
今回の検索ではタイトルと説明の両方まとめて検索するので、インデックスも両方まとめて１つ作ります。 ｍｙｓｑｌクライアントの中での作業になります。 約２０万レコードあるけれど２分かからない程度でした。 既存のデータ入りテーブルに後付けでインデックスを追加するほうがインデックス付きテーブルにデータを追加するより所要時間が短いらしい。

（データベースを選択した上で）
ALTER TABLE movie ADD FULLTEXT INDEX idx_title_description(title, description) WITH PARSER mecab;

バイナリ検索と、サイトに入力されたキーワードをすべてＡＮＤ条件にするため、キーワード単位に分割してプラス「+」を前置します。 まあ、適当にググってください。 ウチのサービスで当初エラーが出て色々試して分かったのが MATCHとAGAINSTが一体のもので、しかもWHERE句の中で他の条件とＡＮＤで接続する必要 があったこと。 サイトの中で使っているクエリはこんな感じです。 「?」の所は検索実行の際にキーワードが埋まります（セキュリティ上、ＭｙＳＱＬにクォート付けて埋めてもらう）。

SELECT videoid, post, title FROM movie WHERE MATCH(title, description) AGAINST(? IN BOOLEAN MODE) AND 85 <= duration AND duration < 600 ORDER BY post DESC LIMIT 10

体感的にあまり速くなってない。 それというのも、よくやる検索は除外チャンネルの該当か別テーブルとＪＯＩＮするので時間がかかるし、 チャンネルの除外をしなくても、検索結果を投稿日順に出力する都合で、見つかった全レコードから件数を絞るため仕方ない。 よくある検索サービスの「適合順」と同じにすれば速そうだがうちのサービスは最新のボーカロイド楽曲を知る目的もあるので投稿日順のままで行こう。

改善（？）したサービスがこちら。
Vocaloid Music Search Service ～ボカロ曲検索サービス～