ルモーリン

MySQLの全文検索

投稿:2018-10-17

自作のサービスがレコード件数に比例して検索時間がかかるようになったので改善したい。
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
スライドに載っていないこと
  • my.cnfにloose-mecab-rc-fileを追加した後の再起動で警告が出る(その後の作業で解消)
  • my.cnfに書く変数名の語の間がハイフン「-」とアンダースコア「_」の変数で混在(どちらも可)
  • MeCabを辞書と呼んでいる(形態素解析エンジンのはず→MeCab: Yet Another Part-of-Speech and Morphological Analyzer
こちらのブログを参考にインデックスを追加します。
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 ~ボカロ曲検索サービス~