Cache for your database queries

When there is a lot of people on your site, they read many articles (which is good). That leads to big number of database retrievals and your database is potential bottleneck (which is bad).

So what will we do about it? The best way is to cache the database results, so when first user reads an article, it is fetched from the database, but when the same article is requested again, then we read it from the cache and thus allowing our MySql server to rest a bit.

There are many types of caching mechanisms like memcached, APC, eAccelerator but for the simplicity of this tip, we will use plain file to hold our cache data. This is enough in most of cases.

So let’s see how our caching class looks like:

[code lang=”php”]
/**
* @desc Class that implements the Cache functionality
*/
class Cache {

/**
* @desc Function read retrieves value from cache
* @param $fileName – name of the cache file
* Usage: Cache::read(‘fileName.extension’)
*/
function read($fileName) {
$fileName = ‘/path/to/cache/folder’.$fileName;
if (file_exists($fileName)) {
$handle = fopen($fileName, ‘rb’);
$variable = fread($handle, filesize($fileName));
fclose($handle);
return unserialize($variable);
} else {
return null;
}
}

/**
* @desc Function for writing key => value to cache
* @param $fileName – name of the cache file (key)
* @param $variable – value
* Usage: Cache::write(‘fileName.extension’, value)
*/
function write($fileName,$variable) {
$fileName = ‘/path/to/cache/folder’.$fileName;
$handle = fopen($fileName, ‘a’);
fwrite($handle, serialize($variable));
fclose($handle);
}

/**
* @desc Function for deleteing cache file
* @param $fileName – name of the cache file (key)
* Usage: Cache::delete(‘fileName.extension’)
*/
function delete($fileName) {
$fileName = ‘/path/to/cache/folder’.$fileName;
@unlink($fileName);
}

}
[/code]

Save above code as cache.php. We’ll use it later.

Code is well commented so I will not explain the details. Notice that in function write we use function serialize and in read unserialize . As PHP manual says:

Serialize generates a storable representation of a value. This is useful for storing or passing PHP values around without losing their type and structure.

So we are just adjusting PHP value in more “storable” way.

Here is how you can use this in your application:

[code lang=”php”]
// include the class
require_once(‘cache.php’);
//instantiate it
$cache = new Cache();
//let’s check if some cache file exists
$data = $cache->read(‘test_17.tmp’); //17 is id of the article, for example
// it doesn’t exist, fetch data from database
if (empty($data)) {
$sql = “SELECT image, user_id, content FROM table WHERE id = 17”;
$data = $db->select_list($sql); // just an example
$cache->write(‘test_17.tmp’, $data);
}
// then use the data as you would normally
foreach($data as $row) {
……..
}
[/code]

This way you will gain some speed and your database server will thank you. But there is a catch! It is very, very important not to forget to clear this cache if something gets changed on that particular article. So do not forget to add this lines after your update the article:

[code lang=”php”]
// include the class
require_once(‘cache.php’);
//instantiate it
$cache = new Cache();
//updating the article example
$data = ‘some array for updating’;
$db->update_record(‘table’, $data);
$cache->delete(‘test_17.tmp’);
[/code]

Now you clear the cache and the new version of the article will appear, and the new Cache file will be created.