perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff <modp...@aquabolt.com>
Subject Re: Database transaction across multiple web requests
Date Fri, 31 Mar 2006 13:42:40 GMT
-------- Original Message --------
From: tomas@tuxteam.de (Tomas Zerolo)
To: modperl@perl.apache.org
Subject: Database transaction across multiple web requests
Date: 31/3/2006 12:59

> (a) Create a database cursor and page through it
> (b) Repeat the query (making sure there is a sort criterium) at each
>     page request, starting at a variable offset and limiting the
>     result set
> (c) Do the whole query at once, putting the results in some kind
>     of array.
> 

MySQL's query cache was designed to do this for you.

Your application simply uses approach (b) and MySQL does the rest 
automatically. So if you

   SELECT * FROM mytable WHERE something='complex' LIMIT 0,30;

and then on another page / connection:

   SELECT * FROM mytable WHERE something='complex' LIMIT 30,30;

and then...

   SELECT * FROM mytable WHERE something='complex' LIMIT 60,30;

The main hit is on the first query, and provided that the data is not 
updated on the server, query 2 and query 3 are served directly from the 
cache. No need to worry about which connection the query was issued on, 
or locking, or keeping a transaction open etc etc.

You can tune the amount of db server cache memory etc, but the defaults 
are usually ok.

Obviously if the underlying data changes, the server re-executes the 
query and you get what you asked for (so your pagination / position can 
change if records are deleted, added or sort values change).

Don't know if this helps you though, as you are not using MySQL.

Regards
Jeff



Mime
View raw message