DBI

[Jacinta Richardson]

On Fri, 2 Apr 2004, Brendon Oliver wrote:

> I'm not familiar with other DBs but from experience using postgresql this is 
> relatively easy.  Just use the LIMIT and OFFSET clauses of the SELECT 
> statement.  LIMIT translates to your number of records per page, OFFSET tells 
> it where to start retrieving records.
> 
> Eg.  SELECT * from my_table LIMIT 10 OFFSET 40
> 
> will give you your "go to results 40-50" request (more correctly, records 40 
> thru 49 inclusive).   You just need to cache the limit / offset somewhere for 
> possib;e re-use between page requests.

There's a missing caveat here...  If you don't sort (ORDER BY) your select
results then there isn't a lot of promise that items 40-50 won't
necessarily be the same (coincidentally) as items 1-10.  Some databases
may vary, but most of them don't promise any kind of internal order.

If you _do_ order your results then remember that items 40 - 50 are not
necessarily the same as the items 40 - 50 of your original select as
someone else may have added a few "earlier" items into the database since
then - or removed them.

Also, if what you're selecting from my_table is very large, selecting it,
sorting it and displaying a handful of results each time can put a lot of
load on your system.  If this is an issue, and if you can afford to not
show new items absolutely immediately, you may want to select without
limit, pull out the bits you care about and cache the rest.  Every x
seconds/minutes/hours (as appropriate) the entry in the cache expires and
you do your select, sort and recache again. 

I hope this helps.

	Jacinta

--
   ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
    `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
    (_Y_.)'  ._   )  `._ `. ``-..-'   |      +613 9354 6001         |  
  _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
(il),-''  (li),'  ((!.-'              |   www.perltraining.com.au   |