2008/5/19 Mitch Jackson <[EMAIL PROTECTED]>: > 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/" _______________________________________________ List: [email protected] Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[email protected]/ Dev site: http://dev.catalyst.perl.org/
