Mysql+PHPで件数取得：COUNT mysql_num_rows FOUND_ROWS 比較

HOME ＞ (旧)開発者blog ＞ Mysql+PHPで件数取得：COUNT mysql_num_rows FOUND_ROWS 比較

Mysql+PHPで件数取得：COUNT mysql_num_rows FOUND_ROWS 比較

※以前別の場所で書いた文章を備忘的に書き記しておきます。

【投稿年月日】2009-03-14　【ジャンル】PHP/MySQL

　検索サイト等のページスイッチは、

「 1,000 件中 1 - 20 件」

のように、全体件数と表示中のデータ件数が併記される場合が多い。ところが、レコード数が多くなったり、SQL文が複雑になると、全体件数の取得にかかるスピードが物凄く遅くなる場合がある。
　と言う訳で、どのような方法で全体件数を取得するのが高速なのかを調べてみた。

　環境はMysql+PHP。候補は次の３つ。

COUNTを使う。
mysql_num_rowsを使う。（*ＰＨＰ関数）
FOUND_ROWSとSQL_CALC_FOUND_ROWSを使う。

サンプル

※以下のSQLの結果、及び、LIMITの制約を外した場合の結果（＝全体件数）を取得する。

SELECT id, name, area FROM company ORDER BY id LIMIT 20;

ケース１　COUNTを使う

$query = 'SELECT id, name, area FROM company ORDER BY id LIMIT 20';
$q1 = 'SELECT COUNT(*) FROM company'; //全体件数を取得するシンプルなSQLクエリを別途用意
$rnum1 = mysql_query($q1);
list($num) = mysql_fetch_row($rnum1);
switch($num) {
case "" :
echo "Not Found.";
break;
default :
echo "<h2>次の20件を含め、全体では ".$num." 件あります。</h2>";
$result1= mysql_query($query);
while($r1 = mysql_fetch_assoc($result1)) {
echo htmlspecialchars($r1['id']." ".$r1['name']." ".$r1['area'], ENT_QUOTES, "UTF-8")."<br />";
}
}

ケース２　mysql_num_rowsを使う

$query = 'SELECT id, name, area FROM company ORDER BY id LIMIT 20';
$q2 = 'SELECT id FROM company ORDER BY null'; //全体件数を取得するシンプルなSQLクエリを別途用意
$rnum2 = mysql_query($q2);
$num = mysql_num_rows($rnum2);
switch($num) {
case "" :
echo "Not Found.";
break;
default :
echo "<h2>次の20件を含め、全体では ".$num." 件あります。</h2>";
$result2 = mysql_query($query);
while($r2 = mysql_fetch_assoc($result2)) {
echo htmlspecialchars($r2['id']." ".$r2['name']." ".$r2['area'], ENT_QUOTES, "UTF-8")."<br />";
}
}

ケース３　FOUND_ROWSとSQL_CALC_FOUND_ROWSを使う

$query = 'SELECT SQL_CALC_FOUND_ROWS id, name, area FROM company ORDER BY id LIMIT 20'; //SQLクエリは１つだけ
$result3 = mysql_query($query);
$num_query = mysql_query('SELECT FOUND_ROWS()');
list($num) = mysql_fetch_row($num_query);
switch($num) {
case "" :
echo "Not Found.";
break;
default :
echo "<h2>次の20件を含め、全体では ".$num." 件あります。</h2>";
while($r3 = mysql_fetch_assoc($result3)) {
echo htmlspecialchars($r3['id']." ".$r3['name']." ".$r3['area'], ENT_QUOTES, "UTF-8")."<br />";
}
}

検証結果

　いろいろと試してみて分かったことを備忘的に残しておく。

　一般的には、FOUND_ROWS（＋SQL_CALC_FOUND_ROWS）を使うと、MySQLの内部処理が少なくて済む（＝速い）と言われている。

FOUND_ROWS （SQL_CALC_FOUND_ROWS）
dev.mysql.com/doc/refman/5.1/ja/information-fun...

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

　これを読む限り、全体件数を取得するために全く別のSQLクエリを発行するよりも、FOUND_ROWS（＋SQL_CALC_FOUND_ROWS）を使ってSQLクエリを再利用（と言っていいのだろうか？）した方が効率的に思える。

　ところが、SQLクエリによっては、FOUND_ROWSを使うことで、とんでもなく時間がかかる場合があるし、非効率的な場合もある。例えば以下のようなケース。

1. 取得するフィールド数が多い場合。: 　SQLクエリで取得するフィールド数が多いと、当然ながら速度が遅くなる。よって、全体件数を取得するSQLクエリを別途発行することで、フィールド数を最小限にすることを検討する。
2. ページスイッチが多く使われる場合。: 　FOUND_ROWSを使う場合、ページごとに（1ページ目も2ページ目も）全件取得を行なうので、MySQLは取得した全体件数をキャッシュしない。ページスイッチが多用されるサイトならば、これって非効率的。よって、取得した全体件数をMySQLにキャッシュさせるようなSQLクエリを、別途発行することを検討する。
3. 複雑なSQL文の場合。: 　例えばleft join等で自己結合している場合など、FOUND_ROWSを使うと猛烈に遅くなるSQLクエリが存在する。よって、全体件数を取得するSQLクエリを別途発行することで、可能な限りシンプルなSQLクエリにすることを検討する。

　逆に以下の場合は、FOUND_ROWSを使う方が速いことが多かった。

取得するフィールド数が少ない場合。
シンプルなSQLクエリの場合。

まとめ

１つのSQLクエリを使いまわすのではなく、全体件数を取得するためにSQLクエリを別途発行することを検討する。
全体件数を取得するSQLクエリでは、取得するフィールド数を最小限（１個）にする。「SELECT * WHERE ～」でmysql_num_rowsするのは最悪。
全体件数を取得するSQLクエリでは、可能な限りシンプルなSQLクエリにする。left join等で自己結合してる場合は、可能な限りleft joinしているテーブルを外したりする。
全体件数を取得するSQLクエリでは、無駄にORDER BYを使わない。（使うならば「ORDER BY null」）

　ちなみに「EDIUNET」や「EDIUNET.COM」のページスイッチでは、全体件数を取得するのにFOUND_ROWSを使っておらず、全体件数を取得するために、SQLクエリを別途発行しています。また、自己結合の有無やWHERE句によって、細かくSQLクエリを変えています。

HOME ＞ (旧)開発者blog ＞ Mysql+PHPで件数取得：COUNT mysql_num_rows FOUND_ROWS 比較

EDIUNET | PHP/MySQL | 独り言 | 提供サービス | JavaScript

知足 (tisoku.net)