[Catalyst] Pager and Cache

[demerphq]

2008/5/20 demerphq <demerphq at gmail.com>:
> 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.

To clarify a touch using this kind of logic you can page in one
direction at a time only and cant step back to see previous pages
without reversing direction. Which just adds to the complexity by
requiring another variable be tracked (the direction).

Cheers,
yves




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