Re: [Phplib-users] Caching MySQL queries

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

Ok, using the filecacheclass from
http://phpclasses.upperdesign.com/browse.html?file=1049 and
http://phpclasses.upperdesign.com/browse.html?file=1048 (for docs, I
think you need a login to get at the site).

I edited db_mysql.inc and added some calls to this class. *It is
working* using the query and next_record functions but with one cavaet.
If a large number of rows are stored in the cache it takes ages for the
cache to walk through them. 

This is a first try at doing this, and I wanted to do it as simply as
possible:

the query() function is called first.
 I use the functions in the cache class above to see if that function is
cached. If so, I set $this->Query_ID to "cache" and a few of the other
vars too. I return $this->Query_ID.

Next, user calls next_record()
This function checks if $this->Query_ID is set to "cache" and if so will
reload the cache and pick out the correct row and return it in the
$this->Record array. Otherwise, it goes to the database, grabs a row and
stores it in the cache.

That's the simple explanation. I serialize the array and base64_encode
it before caching it and seperate each record by a "\n". When I read it
back I split the cache by "\n" and eval() the correct row. Not the best
way of doing it but it's the most obvious way I thought of and the
quickest to see if it'd work :)

Anyone object to me posting a diff against phplib7.2d db_mysql.inc to
the list? Brian - I'll mail you the full file for convenience.

Donncha.

Brian Popp wrote:
> 
> Sounds like an excellent idea and I'd be interested to see how much of a
> performance difference it would make. I'm guessing "a lot" on some of my
> more query intensive sites. If nobody else does it first (or already has), I
> may try to play around with this and see what I can come up with.
> 
> -----Original Message-----
> From: Donncha O Caoimh [mailto:don...@tr...]
> Sent: Friday, October 26, 2001 4:51 AM
> To: php...@li...
> Subject: [Phplib-users] Caching MySQL queries
> 
> 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