「使われていないカラムのSelectをやめる」と「ページネーションでのOFFSET問題」を考えて、SubqueryでページのSelectを作りました。「ページネーションでのOFFSET問題」とは、OFFSETで指定する值が大きくなるにつれSQLの実行結果が遅くなるという罠です。なぜなら、MySQLはOFFSETが指定されている場合、そのOFFSETまでのレコードを全て読み込んでしまうからです。
SELECT `user_id`, `user_description`, `name_katagana`, `name_kanji`, `email`, `update_time`, `user_status`
FROM `users`
ORDER BY `update_time` DESC
LIMIT 6 OFFSET 1000
それでJOINのONで取得するレコード数がもっと少なくなるように絞り込む方法を使いました。
SELECT `record_id`,`records`.`update_time`, `description`, `user_pic`, `publisher_id`, `user_loginid`, `user_status`
FROM `users` INNER JOIN (
SELECT `user_id`
FROM `users`
ORDER BY users.update_time DESC
LIMIT 6
OFFSET 1000 ) AS `a`
ON (`users`.`user_id` = `a`.`user_id`) ;
また、ページネーションを作ることには「システム全体でどのぐらいのデータ数があるかカウントして表示する」というような機能が必要です。
MySQLでMyISAMを使っていれば
Select count(*) from usersという高速なSQL文がありますが、InnoDBではデープル全体をスキャンする必要があるため、レコード数が多ければ多いほど遅くなります。少量のデータのSELECTでは、SELECT SQL_CALC_FOUND_ROWSというprefixを使って、SELECT FOUND_ROWS()でデータ数を取りますが（Select count(*) from usersより早いです！）、やはり多量のデータのSELECTは遅いです。
それで、新しいカウントテブールを作りました。結果として、高速でページを表示することができました。しかし、そこのデメリットはテブールのINSERT/DELETEは前より遅くなりました。
多量のリストは下記のように実行します。
sub search_with_pager {
....
my ($self, $table_name, $where, $opt) = @_;
my $table = $self->schema->get_table($table_name) or Carp::croak("'$table_name' is unknown table");
my $page = $opt->{page};
my $rows = $opt->{rows};
for (qw/page rows/) {
Carp::croak("missing mandatory parameter: $_") unless exists $opt->{$_};
}
my $columns = $opt->{'+columns'}
? [@{$table->{columns}}, @{$opt->{'+columns'}}]
: ($opt->{columns} || $table->{columns})
;
if ( $opt->{offset_key} ){
my $id = $where->{'+offset_key'}
? [@{$table->{columns}}, @{$opt->{'+offset_key'}}]
: ($opt->{offset_key} || $table->{columns})
;
my ($sql, @binds) = $self->sql_builder->select(
$table_name,
$id,
{},
+{
%$opt,
limit => $rows + 1,
offset => $rows*($page-1),
}
);
my $new_table = '( '.$sql.' ) AS a ';
my $id_name = ${@$id[0]}[0];
my $joins = $opt->{joins};
my @new_joins = [ $table_name => {table => \$new_table , condition => $table_name.".".$id_name." = a.".$id_name } ];
push @$joins, @new_joins;
($sql, @binds) = $self->sql_builder->select(
$table_name,
$columns,
{},
+{
joins => $joins,
}
);
my $sth = $self->dbh->prepare($sql) or Carp::croak $self->dbh->errstr;
$sth->execute(@binds) or Carp::croak $self->dbh->errstr;
my $table_count = $table_name."_count";
my $total_entries = 0+$self->dbh->selectrow_array("SELECT cnt from $table_count ; ");
my $ret = [ Teng::Iterator->new(
teng => $self,
sth => $sth,
sql => $sql,
row_class => $self->schema->get_row_class($table_name),
table => $table,
table_name => $table_name,
suppress_object_creation => $self->suppress_row_objects,
)->all];
my $has_next = ( $rows + 1 == scalar(@$ret) ) ? 1 : 0;
if ($has_next) { pop @$ret }
my $current_page = $page;
my $entries_per_page = $rows;
my $pager = Data::Page->new($total_entries, $entries_per_page, $current_page, $has_next);
return ($ret, $pager);
....
}
