[Catalyst] Pager and Cache

[demerphq]

2008/5/19 Mitch Jackson <perimus at gmail.com>:
> Emmanuel,
>
> In most cases, the pager functionality only selects the table rows
> from the database you intend to display.  If, for example, you ask for
> page 3 of a record set with 10 rows per page, LIMIT 20,10 will be
> added to the end of a MySQL query, asking the database for 10 rows
> starting at row number 20.

This kind of paging doesn't scale well IMO. It works fine for small
data sets but for larger ones is pretty poor in terms of performance.

> If you are having performance issues selecting from your database,
> perhaps your table is not properly indexed?

I dont think that indexes help you when you are paging through large
data sets using LIMIT $offset, $size. The DB has to resolve at least
$offset+$size records in order to resolve the query regardless as to
how the db does it, table scan or index. If this number is large this
type of paging will be quite expensive especially on an aggregated
level as it works out to N/2*(N+1) records have to be "seen" by the
db.

If you want to exploit indexes in paging properly you need to involve
an index in the search criteria and remember the last fetched value.
IE:

  select * from Foo where id >= last_id_fetched LIMIT $size

Of course this approach is more susceptible to the data changing
behind your back, but all paging suffers this problem, but unlike
"standard paging" is has the problem that its not "reversible" in the
same way that  LIMIT $offset, $size is, resulting in "uni-directional"
paging. But it has the strength that it is as fast as the db can
resolve the query, the DB never "looks" at more records than you can
see. I suspect the OP might have this kind of situation, and would
benefit from this type of paging.

Cheers,
yves

-- 
perl -Mre=debug -e "/just|another|perl|hacker/"