ルモーリン
ホーム更新Perlサービスランドナーコースガイド鉄ゲタ自転車Linuxリンク連絡先

MySQLの全文検索

背景

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

MySQLをバージョンアップ

MySQLを5.5から5.7にバージョンアップしました。 5.5は英語でMyISAMだけ、5.6は英語でInnoDBもOK、5.7でようやく日本語をサポートしました。 「MyISAMだけ」とか「MeCabのインストールが必要」といった古いブログがあるので記事の投稿年をよく見ましょう。 5.7ならMeCabも同梱してあるので手軽に使えます。

機能のセットアップ

こちらのスライドが丁寧に説明されていて分かりやすかったです。 n-gramとMeCabの二択なのでMeCabを選択しました。 よく見るとオラクルはMySQLの開発に参加してるトコだった。
MySQL :: MySQL 5.6 リファレンスマニュアル←日本語版は5.6
スライドに載っていないこと

インデックスを追加

こちらのブログを参考にインデックスを追加します。
FULLTEXT INDEXは一個ずつ作ろう(by MySQL5.7) - 41から始めました
今回の検索ではタイトルと説明の両方まとめて検索するので、インデックスも両方まとめて1つ作ります。 mysqlクライアントの中での作業になります。 約20万レコードあるけれど2分かからない程度でした。 既存のデータ入りテーブルに後付けでインデックスを追加するほうがインデックス付きテーブルにデータを追加するより所要時間が短いらしい。
(データベースを選択した上で)
ALTER TABLE movie ADD FULLTEXT INDEX idx_title_description(title, description) WITH PARSER mecab;

クエリの組み方

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

結果

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

おまけ

改善(?)したサービスがこちら。
Vocaloid Music Search Service ~ボカロ曲検索サービス~