« An Illustrated guide to IPSEC | Main | Do you flickr? »

Database query caching

I'm busy spending time adding more features to my PHP Application Framework called the Tshukudu Application Framework which currently is quite a liteweight PEARish framework for PHP4 and PHP5. It's moving towards dropping PHP4 in the near future.

I've spent some time fiddling with the idea of caching database query results to disk instead of to a memcached servers when in a shared hosting environment to reduce load on a database server when looking up data which does not change very often. By caching database queries, it reduces the amount of work the database has to do to look up data considering the larger the dataset, the more data has to flow between MySQL and PHP.

Quite surprisingly I stumbled across an article titled Caching PHP Programes with PEAR on O'Reilly written by Sebastian Bergmann. Interesting read none the less.

So I created a class called DB_Cache which currently just extends PEAR Cache as well as keeping a DB connection open for sending queries to. The Cache_Memcached connects to Memcached servers to retrieve and store data while DB_Cache (should more likely be called Cache_DB_File in reality) caches database result sets to a file on the filesystem via Cache (which it extends).

A couple ideas behind the framework is to provide functionality for speeding up PHP sites by having different levels of caching within a PHP application such as caching content from remote servers, caching database resultsets, etc.

Utilisation of the code looks something like:

require_once 'Tshukudu/DB/Cache.php';
$db_cache = new DB_Cache;
$users = $db_cache->query("SELECT * FROM users LIMIT 0,10", 'getall'));

Ugly but needs a slight rewrite to rather extend DB_MySQL in mycase instead of Cache.


TrackBack URL for this entry:

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)