2008/5/20 demerphq <[EMAIL PROTECTED]>: > 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.
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/" _______________________________________________ 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/
