end0tknr's kipple - 新web写経開発

http://d.hatena.ne.jp/end0tknr/ から移転しました

mysqlのSQL_CALC_FOUND_ROWS で LIMIT句なしで書かれた行数を取得

http://d.hatena.ne.jp/ZIGOROu/20091126/1259218194
http://dev.mysql.com/doc/refman/5.1/ja/information-functions.html#function_found-rows

mysqlって、SQL_CALC_FOUND_ROWS で LIMIT句なしで書かれた行数を取得できるんですね。
ということは、Data::Pageと組み合わせて、次のように書けばいいのかな?

use Data::Page;

my $PER_PAGE = 50;	#1page毎の表示件数

sub get_item_list {
    my ($self,$pno) = @_;

    #とりあえず、total size = $pno * $PER_PAGE にしときます
    my $page = Data::Page->new($pno * $PER_PAGE,$PER_PAGE,$pno);
    my ($item_list,$total_size) = $self->get_page_item_list($page);
    #改めて、total size を使って、Data::Page->new()します
    $page = Data::Page->new($total_size,$PER_PAGE,$pno);
    return ($item_list,$page);
}

#該当pageのitem一覧の取得
sub get_page_item_list {
    my ($self,$page) = @_;

    my $per_page = $page->entries_per_page;
    my $sql_limit =
	"LIMIT $per_page OFFSET ". ($page->current_page -1) * $per_page;

    my $sql =<<EOF;
select SQL_CALC_FOUND_ROWS a.id
from item a
join item_summary a_s on (a.id=a_s.id)
order by a_s.update_time desc
$sql_limit
EOF
    my $sth = $self->{dbh}->prepare($sql);
    $sth->execute();

    my $sql_rows =<<EOF;
SELECT FOUND_ROWS()
EOF
    my $sth_rows = $self->{dbh}->prepare($sql_rows);
    $sth_rows->execute();
    my $row_size = $sth_rows->fetchrow_array();

    my @items;
    while (my $row = $sth->fetchrow_hashref() ) {
	my $item = Hoge::Item->new($self->{dbh},
				   $self->{user},
				   $row->{id});
        push(@items,$item) if $item;
    }

    return \@items, $row_size;
}

SQL_CALC_FOUND_ROWS の速度は、limitなしのsql再実行より、速い

dev.mysql.com と、ipaにその情報が記載されていますが、ページング処理のように総件数を算出する為に、limitなしのsqlを再実行するより、速いようです。

mysql.com

SELECT SQL_CALC_FOUND_ROWS を使用している場合、MySQL は完全な結果セットにいくつ行があるか計算する必要があります。しかし、結果セットをクライアントに送る必要がないため、LIMIT なしでクエリを再度実行するより速く行えます。
http://dev.mysql.com/doc/refman/5.1/ja/information-functions.html

ipa (次のurlには検証結果のグラフもあります)

SQL_CALC_FOUND_ROWSを使用することで、性能は70%近く向上している。
http://ossipedia.ipa.go.jp/capacity/EV0603280115/