FTS3を使って全文検索してみた。
SQLiteの全文検索というと、昔はFTS1とか2とかのモジュールを使用する方式だったみたいだけど、現在はFTS3なるものがsqlite3.exeとかに含まれているので、得に下準備をすることなく使用できるっぽい。
公式の説明
http://www.sqlite.org/fts3.html
参考にしたサイト
http://cast-a-spell.at.webry.info/200806/article_15.html
フルテキストサーチする為にやることは以下の3つ。
・CREATE VIRTUAL TABLE テーブル名 USING fts3 でテーブルを生成する
・INSERTする時に、フルテキスト検索に使いたいカラムに、ヒットさせたいワードを半角スペースで区切りつつ登録
・MATCHを使って検索する
検索方法とかそれなりに用意されているので、そこそこの件数を処理するだけならSQLiteで十分かもしれない。
1. CREATEする
とりあえず、sentenceに文章を、wordsに検索用の単語を入れるようなテーブルを作ってみる
CREATE VIRTUAL TABLE tbl USING fts3(sentence, words);
2. INSERTする
1つ目のカラムに適当な文章を、2つ目のカラムに分かち書き（半角スペース区切り）した文章を入れる。
INSERT INTO tbl VALUES( '今日は良い天気だ', '今日 は 良い 天気 だ' );
この半角スペースで区切った単語たちに対して検索がかけられる。
3. 普通にSELECTする
WHERE句でMATCHを使って検索すると引っかかる
SELECT sentence FROM tbl WHERE words MATCH '今日';
→ 今日は良い天気だ
「今日」では引っかかるけど、「今」では引っかからない
SELECT sentence FROM tbl WHERE words MATCH '今';
→ empty set
複数の検索ワードを使うことも可能
SELECT sentence FROM tbl WHERE words MATCH '今日 天気';
→ 今日は良い天気だ
4. 前方一致でSELECTする
アスタリスクを使えば前方一致で引っかけれる
SELECT sentence FROM tbl WHERE words MATCH '今*';
→ 今日は良い天気だ
前方一致だけで、後方一致は無理
SELECT sentence FROM tbl WHERE words MATCH '*日';
→ empty set
複数の検索ワードを使うことも可能
SELECT sentence FROM tbl WHERE words MATCH '今* 天*';
→ 今日は良い天気だ
5. フレーズ検索でSELECTする
ダブルコーテーションでくくれば、フレーズ検索
SELECT sentence FROM tbl WHERE words MATCH '"今日 は 良い"';
→ 今日は良い天気だ
下記のような繋がってない検索ワードだと引っかからない
SELECT sentence FROM tbl WHERE words MATCH '"今日 天気"';
→ empty set
6. 距離を指定してSELECTする
NEARを使うと、指定された距離内に単語がいないと検索に引っかからない
下記の例だと、2個隣までは引っかかる
SELECT sentence FROM tbl WHERE words MATCH '今日 NEAR/1 良い';
→ 今日は良い天気だ
3個隣の「今日」と「天気」を指定すると引っかからない
SELECT sentence FROM tbl WHERE words MATCH '今日 NEAR/1 天気';
→ empty set
7. 複合条件でSELECTする
ORを使うと、どっちかが含まれていれば検索にヒットする
SELECT sentence FROM tbl WHERE words MATCH '今日 OR 存在しない言葉';
→ 今日は良い天気だ
NOTを使うと、存在しないことみたいな指定になる
と思ったけど、気のせいだったみたいだ
SELECT sentence FROM tbl WHERE words MATCH '今日 NOT 存在しない言葉';
→ empty set
単語の前にマイナス符号を付けると、その言葉が存在しない場合にヒットする
SELECT sentence FROM tbl WHERE words MATCH '今日 -存在しない言葉';
→ 今日は良い天気だ
存在する言葉でマイナスを付ければ、ヒットしない
SELECT sentence FROM tbl WHERE words MATCH '今日 -天気';
→ empty set
8. 性能を見てみる
とりあえずランダムな文字列を生成して、10万件ほどテーブルに突っ込む。
ちなみにSQLiteはトランザクション使わないと大量INSERTがもの凄く遅いので（10分待っても終わらなかったので、途中で一度切ったさ）、入れる時はBEGINとCOMMITを忘れない。
というわけで、10万と1件入った状態で計測してみる。
SELECT count(*) FROM tbl;
→ 100001
この程度のサイズなら、2ミリ秒程度で処理可能らしい
SELECT sentence FROM tbl WHERE words MATCH '今日';
→ 今日は良い天気だ
→ 1.873ミリ秒
前方一致も速度は変わらず
SELECT sentence FROM tbl WHERE sentence MATCH '今日*'
→ 今日は良い天気だ
→ 2.028ミリ秒
LIKEの前方一致検索だと、こんな速度
SELECT sentence FROM tbl WHERE sentence like '今日%'
→ 今日は良い天気だ
→ 194.502ミリ秒
並べて見るとこんな感じ。
1.873
2.028
194.502
というわけで、LIKEと比べると100倍速い。用意したテストデータは、sentenceに20文字、wordsはスペースが6個杯って26文字しか入ってない軽量のもの。これが10万件くらいであればパフォーマンス的には全く問題ないようで。
あと、条件を複数いれると、こんな感じ
SELECT sentence FROM tbl WHERE words MATCH '今* 天気 良*';
→ 今日は良い天気だ
→ 2.955ミリ秒
以上、SQLiteの全文検索を使用してみた際のメモでした。
例文を「今日は良い天気だ」にしたけど、「明日は晴れるかなぁ？」にしておけば良かったと少し後悔した。そっちの方が天和とか出そうだから。
2の2の核分裂！！！