Page 1 of 1

Caching reasonably static data from MySQL DB in PHP

Posted: Mon Nov 07, 2011 7:45 pm
by mecha_godzilla
Hi,

Apologies if this topic has already been covered before but I couldn't find anything relevant when I searched...

In my application I have a lot of drop-down menus which are populated with options retrieved from the DB. When I want to generate a menu I retrieve all of the values in my (say) videogame categories table, then I loop through these to build the code for the menu; one of the options in the menu will be selected by default depending on whether the record is being created or edited.

My question is this - at the moment, I'm running a query to retrieve the values for every drop-down menu that the script needs to generate and this is a bit redundant because the available options don't change very often. What I'd like to do is find a way to cache the options locally (in a file or in memory) once, then each time I run the add/edit record scripts they look at this file instead of running a query, unless the options in the DB have been updated.

Has anyone tried to do anything like this already? I thought about caching the queries in the DB but the application processes so many different types of queries that this would probably just slow things down, and it seems to make more sense to generate the queries once and save them somewhere so that they'll always be available until the options change i.e. like an included file. This approach assumes that local file access will be much quicker because the disk controller on the server will always have the file in its cache, but I can't quite work out how the script would be able to tell whether the options in the database have changed without querying it? :)

Thanks in advance,

Mecha Godzilla

Re: Caching reasonably static data from MySQL DB in PHP

Posted: Tue Nov 08, 2011 2:32 am
by Christopher
Take a look at Zend_Cache.

Re: Caching reasonably static data from MySQL DB in PHP

Posted: Tue Nov 08, 2011 8:03 am
by Eric!
I haven't used zend_cache, but I built a class that does some simple cache functions for PDO as an experiment. I used a fixed cache expiration time per cached query just so things don't get too stale. On pages that make a lot of SQL calls that are all cached I see about a 5% to 8% speed up but my code could be vastly improved.

Basically I hash the query and then check the cache directory for matching hashes and return the data if the age of the cached file hasn't expired. The caching would run faster if I skipped the hash process and used a different lookup system, but I just ported the code over from another caching tool that I made to see how it would work with SQL. Without seeing exactly what you are doing I'm not sure caching would help you when you are editing or adding records. I used it only for SELECT type functions where there are no db modifications, just reads.

Re: Caching reasonably static data from MySQL DB in PHP

Posted: Tue Nov 08, 2011 3:05 pm
by mecha_godzilla
Thank you both for your replies.

I haven't used Zend Framework yet but I'll take a look at it - I was always a bit put off by it because a lot of the functionality in Zend Server seemed to be in the commercial version and not the community release, but I'll be honest and say that I didn't really look into it in too much detail so I'll check if this is still the case. That said, I've recently downloaded a couple of other frameworks so they might be able to do what I want but - and this is just me I guess - I don't like frameworks per-se as my logic and their logic never quite seem to meet in the middle. Of course, that might just be because my brain doesn't work properly :mrgreen:

Eric! - all my cached queries would be doing is SELECTing data, so your hash/expiry scheme might do the job. I've just started the process of objectifying all my queries in the existing scripts so that would probably tie-in quite nicely with it. I looked at something yesterday that's supposedly the "logical" successor to pdo (ADOdb) so I'll see if this has any kind of caching facility within it.

Thanks again,

M_G

Re: Caching reasonably static data from MySQL DB in PHP

Posted: Tue Nov 08, 2011 3:10 pm
by Christopher
You don't need to use Zend Framework to use Zend Cache. It is a stand-alone component. In fact other frameworks, like Symfony2, use Zend Cache.

Re: Caching reasonably static data from MySQL DB in PHP

Posted: Tue Nov 08, 2011 5:27 pm
by mecha_godzilla
Christopher wrote:You don't need to use Zend Framework to use Zend Cache. It is a stand-alone component. In fact other frameworks, like Symfony2, use Zend Cache.
Ok, that shows how much I know :) Do you know where I can download Zend Cache from, or do I need to download Zend Framework first?

Re: Caching reasonably static data from MySQL DB in PHP

Posted: Tue Nov 08, 2011 7:28 pm
by Eric!
Tutorial: http://www.joeyrivera.com/2009/caching- ... and-mysql/

I should mention the speed-up you get depends on your database loads and the complexity and size of the queries. What I measured was on a pretty idle system.

Re: Caching reasonably static data from MySQL DB in PHP

Posted: Wed Nov 09, 2011 2:49 pm
by mecha_godzilla
Thanks - that looks like just what I need :) I'll take a look at ADOdb if I can't get Zend Cache working...

What I'm really trying to achieve with the caching is to limit the number of times my scripts need to run DB queries because I've sometimes got at least 10 drop-down menus in certain pages and even though the queries run very quickly, that approach doesn't scale up very well if the application has lots of users on it at the same time.