[Phplib-users] Caching MySQL queries

[Donncha O C. <don...@tr...>]

I'm not sure if this has been brought up before so I'll soldier on. I
did a quick search of google and some other sites and don't remember it
ever coming up here.

Has anyone considered doing a caching layer for MySQL queries through
the phplib interface? Here's some ideas:

1. Add a new function: fetchall( $query ) to the DB_sql class. This
class will act like the Perl function of the same name and fetch the
entire result set of a query and return it in a 2d array.
2. This function will also cache the result set of the query somewhere
and if the same query is sent to that database within a certain length
of time, return the cache instead of hitting the database. Generating an
md5 of the query should be sufficient to match queries up.
3. None of the caching should be seen by the developer except perhaps
through class variables or functions.
4. Since databases and tables aren't created often the TTL (time to
live) of any particular table cache could be hard coded in a config file
for very fast access. It of course could also be stored in a db which
would usually only ever be read from, and modified once in a blue moon.

The standard query() function could be used along with the next_record()
function but if the result set isn't completely "walked" through then
the cache wouldn't be complete. "Finish" markers would ensure this
doesn't happen.

Any one of a number of caching scripts could be used and I'm just
throwing this out to the list as an idea I may work on later on..

Donncha.