mysqlで結果の行数をカウントするsql文は、
seletc count(*) from foo;
で取得するのが最も簡単ではあるけれど、レコード数が膨大な場合にはパフォーマンスの面で問題になる場合がある。パターンマッチングで検索する場合、Like演算子を使って
select count(*) from foo where bar like '%baz%';
という風にレコード数をカウントした場合、パフォーマンスは体感でわかるほどに悪くなる可能性がある。例えば200万レコードから実際にLike演算子を使って行数をカウントしてみると、
mysql> select count(*) from aTable where aTitle like '%windows%'; +----------+ | count(*) | +----------+ | 941 | +----------+ 1 row in set (5.64 sec)
という結果になった。行数のカウントをするだけで約6秒も待たないといけないのはちょっとつらい。何か高速にカウントする術はないかと探してみると、mysqlには行数をカウントするためのSQL_CALC_FOUND_ROWSという修飾句があった。これを使って行数を取得する方法は、まずSQL_CALC_FOUND_ROWSをつけてselect文を発行して、そのあとでSELECT FOUND_ROWS()を発行すると行数が取得できるという仕組みらしい。実際にテストしてみると、
mysql> select SQL_CALC_FOUND_ROWS aTitle from aTable where aTitle like '%windows%' limit 1; +---------------------------------------------------------------------------------+ | aTitle | +---------------------------------------------------------------------------------+ | foo-bar-baz | +---------------------------------------------------------------------------------+ 1 row in set (5.61 sec) mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 941 | +--------------+ 1 row in set (0.00 sec)
結果はほとんど変わらなかった。ただ、試しにアスタリスク（*)を使って全フィールドを指定して取得してみると、2秒強時間を短縮することができた。全
mysql> select SQL_CALC_FOUND_ROWS * from aTable where aTitle like '%windows%' limit 1; +--------+-------------------------------------------------------+----------------------------------------------------------+---------+----- -------+ | foo +--------+-------------------------------------------------------+----------------------------------------------------------+---------+----- -------+ bar +--------+-------------------------------------------------------+----------------------------------------------------------+---------+----- -------+ 1 row in set (3.02 sec) mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 941 | +--------------+ 1 row in set (0.00 sec)
フィールド取得は無駄が多くて時間がかかるかと思っていたが逆に速いのか。6秒かかっていたのが3秒になったというのは前進したといえるけれど、なんとか1秒以下にすることはできないだろうか。さらに調べてみると、match ～ against 句という全文検索をすることができるものを見つけた。http://dev.mysql.com/doc/refman/4.1/ja/fulltext-search.html
とにかく行数のカウントを早くしたいのでテストしてみる。まずはテーブルにfulltextを追加する。
mysql> alter table aTable add fulltext(aTitle);
200万レコードもあったので追加終了まで相当時間がかかった。そして、行数のカウントをためしてみる。
mysql> select count(*) from aTable where match(aTitle) against('windows');
+----------+
| count(*) |
+----------+
| 85 |
+----------+
1 row in set (0.02 sec)
速い。以前に比べるまでもないくらい全文検索は高速だということがわかった。しかし、検索の精度には疑問が残る。like演算子を使って検索した場合には941件が該当したのに対し、全文検索では85件しか該当しなかった。正しいのは941件データが出たほうで、全文検索では多くがとりこぼしてしまったということになる。検索結果に多少のあいまいさが許される検索エンジンなどはともかく、正確なデータを取得する場合にはこれは使えない。速いが正確ではないデータと、多少時間がかかるが正確なデータでは、作るものにもよるが後者に軍配が上がるだろう。あの速さで正確にデータが取得できれば何の問題もないのだけれど、それは無理な話なのだろうかな。他のデータベース、oracleやpostgres、sql serverなどはどうなのかわからないので、もしそれが可能ならば試してみるけど手間がかかるなぁ。
検索するときにLike演算子を使って行数をカウントするのが何故時間がかかるかというと、200万レコードあったら200万レコードすべてパターンにマッチするかどうかを見る必要があるためであり、インデックスを使うこともできず、これはちょっと八方ふさがりかもしれない。（普通のselect文では200万レコードすべてを検索して表示することなどなく、limit演算子などを使って件数を区切って表示するため、そういう場合のＬｉｋｅ演算子でのパターマッチングでは条件の件数を満たせば処理が終わるから動作は速い。countでLike演算子を使うと200万レコードを全てチェックしないと処理が終わらないので、検索処理に時間がかかるのは避けられない。googleなどの検索エンジンは全文検索なのであんなに速いのだろう。）
その後
tritonを導入することにした。全文検索は精度に問題がある、と思っていたが、mecabを使うことで日本語にもヒットさせることができるようになるとのこと。レコード数が200万程度であれば、Like演算子でインデックスを使った前方一致やＤＢのチューニングやマシンの性能アップをするなどで対応できるかもしれないけれど、1000万や1億レコードを扱うという場合には、とてもじゃないが対応しきれない。ゆえに、全文検索を使うことに決めた。
