« mylo（マイロ）が欲しい！ | メイン | 第20回参議院議員通常選挙 »

2007年07月29日

COUNT(*) の代わりに FOUND_ROWS() を利用する

MySQL データベースからデータを取得するとき、オフセット（LIMIT）を利用する場合が多いと思う。同時に、条件に合致するデータの件数を知りたい場合も多いだろう。そのような時、そのようなクエリを投げれば良いのだろうか？

・データ取得

SELECT
  *
FROM
  table
WHERE
  field = 5
LIMIT 0, 10

・件数取得

SELECT
  COUNT(*)
FROM
  table
WHERE
  field = 5;

LIMIT を利用してデータを取得した後、別途 COUNT() 関数を利用したクエリを投げる事により、合致するデータの件数を知ることが出来る。このような単純なクエリの場合、各句をそれぞれ変数に格納していれば、特に件数取得用のクエリ文生成が面倒になることは無いだろう。

$select_data =
  'SELECT '.
  '  * ';
$select_count =
  'SELECT '.
  '  (*) ';
$from =
  'FROM '.
  '  table ';
$where =
  'WHERE '.
  '  field = 5 ';
$limit =
  'LIMIT 0, 5 ';
$sql_data = $select_data . $from . $where . $limit;
$sql_count = $select_count . $from . $where;

単純なクエリの場合は、この方法でも良いのだが、複雑になったらどうだろうか？

・データ取得

SELECT
  COUNT(*), field2
FROM
  table
WHERE
  field1 = 5
GROUP BY
  field2
LIMIT 0, 10

・件数取得

SELECT
  COUNT(DISTINCT field2)
FROM
  table
WHERE
  field1 = 5

GROUP BY 句がデータ取得のクエリ文に入る場合、前記のように各句を変数に格納していても、単純に件数取得用のクエリ文を生成することが出来ない。なぜなら、件数をカウントする COUNT(*) が GROUP BY でまとめた結果をカウントしてしまうからだ。正常にカウントするためには、GROUP BY を外す必要があり、データ取得用のクエリ文と、件数取得用のクエリ文は大きく異なる事になる。これでは、保守性が非常に悪い。今回の場合は DISTINCT 関数を用いれば解決できるが、複数のテーブルを結合したり HAVING が含まれたりする場合は、そう単純じゃない（はず）。

MySQL 4 から、新たに FOUND_ROWS() という関数が追加された。この関数を利用すると、簡単に合致するデータの件数を取得することが出来る。

・データ取得

SELECT SQL_CALC_FOUND_ROWS
  COUNT(*), field2
FROM
  table
WHERE
  field1 = 5
GROUP BY
  field2
LIMIT 0, 10

・件数取得

SELECT
  FOUND_ROWS();

データ取得用のクエリ文に SQL_CALC_FOUND_ROWS を付加していれば、次のクエリ文で FOUND_ROWS を呼び出すだけで、合致する件数を取得することが出来る。

SELECT SQL_CALC_FOUND_ROWS を使用している場合、MySQL は完全な結果セットにいくつ行があるか計算する必要があります。しかし、結果セットをクライアントに送る必要がないため、LIMIT なしでクエリを再度実行するより速く行えます。

結果セットを送る必要の無い COUNT() との比較については、リファレンスマニュアルには書かれていない。しかし、オープンソース情報データベース OSS iPedia での検証によれば、約 70% のパフォーマンス向上が確認できたとの事（InnoDB によるものなので MyISAM は未検証）。

検索エンジンの類を MySQL で開発している場合、この FOUND_ROWS, SQL_CALC_FOUND_ROWS の利用は必須であろう。クエリのキャッシュ（SQL_CACHE）を利用している場合も、正常に作動する。

ま。アプリケーションから MySQL にアクセスする際に、クエリ文をべた書きしているケースは少なくなってきていると思うけど。僕は、未だに DBI を利用して、べた書き…。

【関連情報】
・11.10.3. 情報関数 - MySQL 5.1 リファレンスマニュアル
　http://dev.mysql.com/doc/refman/5.1/ja/information-functions.html
・4.13.1. クエリ キャッシュの動作 - MySQL 5.1 リファレンスマニュアル
　http://dev.mysql.com/doc/refman/5.1/ja/query-cache-how.html
・MySQL 行カウント SQL_CALC_FOUND_ROWS の効果 - OSS iPedia
　http://ossipedia.ipa.go.jp/capacity/EV0603280115/

2007年07月29日 17:05 | Programming

トラックバック

コメント

貴重な情報ありがとうございます。
非常に参考になりました。

ちなみにですが、最後のデータ取得のSQLは、
SELECT SQL_CALC_FOUND_ROWS
COUNT(*), field2
FROM・・・
ではなく、
SELECT SQL_CALC_FOUND_ROWS
field2
FROM・・・
だけでよいと思うのですが。

前者だと最後の結果セット
SELECT
FOUND_ROWS();
が1になってしまうと思うのですが。

投稿者 igreks : 2012年02月28日 21:16