まとまりのないブログ

something which something is something

mysql sql

結果の行数をカウントする

投稿日:

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演算子などを使って件数を区切って表示するため、そういう場合のLike演算子でのパターマッチングでは条件の件数を満たせば処理が終わるから動作は速い。countでLike演算子を使うと200万レコードを全てチェックしないと処理が終わらないので、検索処理に時間がかかるのは避けられない。googleなどの検索エンジンは全文検索なのであんなに速いのだろう。)

その後

tritonを導入することにした。全文検索は精度に問題がある、と思っていたが、mecabを使うことで日本語にもヒットさせることができるようになるとのこと。レコード数が200万程度であれば、Like演算子でインデックスを使った前方一致やDBのチューニングやマシンの性能アップをするなどで対応できるかもしれないけれど、1000万や1億レコードを扱うという場合には、とてもじゃないが対応しきれない。ゆえに、全文検索を使うことに決めた。

-mysql, sql

執筆者:


comment

メールアドレスが公開されることはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)

関連記事

no image

mysqlの運用時のトラブルと試行錯誤のメモ

条件 1分間にクエリの実行が30回〜60回程度発生する。 結果のテーブルは7フィールドの数千〜10万件レコード程度のテーブル mysqlのスペックはCPUがcore2duo,メモリが8GB,他のサービ …

no image

mariadbのポートを変更する

mariadbのコンフィグファイルであるmy.cnfにポートの設定を追加する。mysqldセクションとportを下記のように追加して再起動する。そしてmysqlにログインして show variabl …

no image

myisamchk: error: myisam_sort_buffer_size is too small

myisamchkを実行したときに myisamchk: error: myisam_sort_buffer_size is too small Try fixing it by using the …

no image

mysqlのmy.iniの標準値のメモ

mysqlのmy.iniファイルのサンプルの設定ファイルの値がどのように設定されているかチェック。mysqldの設定例を抜き出してみる。設定するときの参考にはなるだろうと思うので。

no image

select文で特定のセルに文字列を挿入する

sqlのselect文で列の値へテーブルの値ではなくて、特定の文字列を挿入した場合のsql文。たとえばテーブルの合計を出したい場合などのとき、1列目のセルの値は文字列で’合計’ …