100000件以上のレコードでのキーワード検索が遅くて困ってます。
ある2つのカラムのどちらかに指定キーワードがあればselectするという条件です。
こんな感じでselectしてます。
mysql> select count(*) from search where text like '%mysql%' or title like '%mysql%';
likeで%を前後に使用してるんでインデックスが適応されない検索をしてます。
結果表示に2〜6秒かかるんで体感出来るくらい遅いです。
そこで全文検索なるものを使用しようかと思いました。
MATCH()関数で検索対象のカラムを指定します。
でも対象カラムはfulltextインデックスがはられていないとダメなんです。
そして検索文字列はAGAINST()で指定します。
全文検索はMySQL3.23.23以上のバージョンから使用可能みたいです。
MyISAMテーブルのみに使用できます。
早速テストしてみました。
使用データーベースはmysql4.0.23で、検索対象テーブルのレコード数は70000件を用意して、2つのカラムを対象にキーワード検索をします。
とりあえず以下のテーブルを作る。
create table search (
title char(128) not null default '',
text char(255) not null default ''
)type=myisam;
テーブル作ったら70000件のデータを挿入。
で、fulltextインデックスをはる。
alter table search add fulltext(title);
alter table search add fulltext(text);
まずは普通にlikeを使用して「mysql」という単語を両方のカラムを対象に検索。
mysql> select count(*) from search where text like '%mysql%' or title like '%mysql%';
+----------+
| count(*) |
+----------+
| 20000 |
+----------+
1 row in set (1.39 sec)
次にMATCH()とAGAINST()を使用した全文検索。
mysql> select count(*) from search where match(title) against('mysql') or match(text) against('mysql');
+----------+
| count(*) |
+----------+
| 20000 |
+----------+
1 row in set (0.46 sec)
結果、53秒速くなりました。
次は、両方のカラムを複合インデックスにしてやってみます。
まず、すでにはられているインデックスを削除。
drop index title on search;
drop index text on search;
改めてfulltextの複合インデックスをはる。
alter table search add fulltext(title,text);
で、全文検索。
mysql> select count(*) from search where match (title,text) against ('mysql');
+----------+
| count(*) |
+----------+
| 20000 |
+----------+
1 row in set (0.23 sec)
通常のインデックスと比べると、23秒速くなりました。
2倍速くなるのは結構な結果ですね。
でもこの全文検索は日本語には対応してません。
実際に日本語を含むデータで検索してもヒットしなかったです。
完璧に英語のみ対応らしいです。
英語のように単語がスペースで区切ってある文字列を対象にしているので、単語がスペースで区切られない日本語はダメみたいです。
どうしても日本語で全文検索をしたいときは、日本語を英語のように単語ごとに区切ってデータの整形をしなくちゃいけないですね。
と、言うわけで日本語が使えないんじゃ使いもんにならないやという事になりましたよ。
日本語整形プログラム作るのもめんどいしなーみたいな。
参考ページ
http://www.itmedia.co.jp/enterprise/0307/03/epn41.html
http://dev.mysql.com/doc/mysql/ja/Indexes.html
http://dev.mysql.com/doc/mysql/ja/Fulltext_Search.html
http://dev.mysql.com/doc/mysql/ja/Fulltext_Restrictions.html
http://dev.mysql.com/doc/mysql/ja/Fulltext_Fine-tuning.html
http://dev.mysql.com/doc/mysql/ja/Fulltext_TODO.html