Caching database result sets - PHP

As you may know, one of the most common approaches used for enhancing the performance of applications, including web- and desktop-based apps, is the implementation of a cache mechanism. This mechanism may use the file system, shared memory, etc. to reduce the number of accesses to a specified resource, such as a relational database.

In the case of the web application being developed here, the simplest way to decrease the number of accesses to its associated MySQL database is by using the framework’s cache class. If you don’t recall how this class was originally defined, below I listed its definition for you:

(Cache.php)

<?php

class Cache

{

private $cachedir = 'cache/';

private $expire = 60;

private static $instance = NULL;

// get Singleton instance of Cache class

public static function getInstance($cachedir = '')

{

if (self::$instance === NULL)

{

self::$instance = new self($cachedir);

}

return self::$instance;

}

// constructor

public function __construct($cachedir = '')

{

if ($cachedir !== '')

{

if (!is_dir($cachedir) or !is_writable($cachedir))

{

throw new Exception('Cache directory must be a valid writeable directory.');

}

$this->cachedir = $cachedir;

}

}

// write data to cache file given an ID

public function set($id, $data)

{

$file = $this->cachedir . $id;

if (file_exists($file))

{

unlink($file);

}

// write data to cache

if (!file_put_contents($file, serialize($data)))

{

throw new Exception('Error writing data to cache file.');

}

}

// read data from cache file given an ID

public function get($id)

{

$file = glob($this->cachedir . $id);

$file = array_shift($file);

if (!$data = file_get_contents($file))

{

throw new Exception('Error reading data from cache file.');

}

return unserialize($data);

}

// check if the cache file is valid or not

public function valid($id)

{

$file = glob($this->cachedir . $id);

$file = array_shift($file);

return (bool)(time() - filemtime($file) <= $this->expire);

}

}// End Cache class

Even though its driving logic is very simple, the cache class permits you to store serialized data in a specified directory during a predefined time period, which can be modified at will. With this caching mechanism available for use, I’m now going to modify the signature of the model class so it can use an instance of the cache class to store in the cache, for at least 60 seconds, the row set returned by its “fetchAll()” method.

The modified version of the model class will look like this:

(Model.php)

<?php

class Model

{

private $db = NULL;

private $cache = NULL;

private $table = 'users';

// constructor

public function __construct(MySQL $db, $table = '')

{

// store database class instance

$this->db = $db;

// store Cache class instance

$this->cache = Cache::getInstance();

if ($table !== '')

{

$this->table = $table;

}

}

// get all rows from specified table

public function fetchAll()

{

// if the cache file is valid fetch records from cache file

if ($this->cache->valid($this->table))

{

return $this->cache->get($this->table);

}

else

{

// otherwise fetch records from database table

$rows = array();

$this->db->query('SELECT * FROM ' . $this->table);

while ($row = $this->db->fetch())

{

$rows[] = $row;

}

// save data set to cache file

$this->cache->set($this->table, $rows);

return $rows;

}

}

// update/insert row

public function save(array $data, $id = NULL)

{

if (!empty($data))

{

// quote strings

foreach ($data as $field => $value)

{

$value = mysql_escape_string($value);

if (!is_numeric($value))

{

$data[$field] = '\'' . $value . '\'';

}

}

// update row

if ($id !== NULL)

{

$set = '';

foreach($data as $field => $value)

{

$set .= $field .'=' . $value . ',';

}

$set = substr($set, 0, -1);

$this->db->query('UPDATE ' . $this->table . ' SET ' . $set . ' WHERE id=' . (int)$id);

}

// insert new row

else

{

$fields = implode(',', array_keys($data));

$values = implode(',', array_values($data));

$this->db->query('INSERT INTO ' . $this->table . ' (' . $fields . ')' . ' VALUES (' . $values . ')');

}

}

}

// delete row

public function delete($id = NULL)

{

if ($id !== NULL)

{

$this->db->query('DELETE FROM ' . $this->table . ' WHERE id=' . (int)$id);

}

}

}// End Model class

If you take a close look at the “fetchAll()” method in the model, you’ll quickly grasp how it works. Essentially, this method will first check to see if there’s some data in the specified cache, and if it’s valid. If this happens to be true, then it’ll retrieve this data directly from the cache; otherwise, the data will be fetched from the database and cached for 60 seconds. This simple use of the cache class will notably reduce the overhead for accessing the MySQL table, and the cache expiration is entirely customizable.

You may be wondering why I decided to cache result sets in the model and not in the controller instead. Well, I did it that way because I’m a strong advocate of delegating to the model all the tasks that handle the data layer. In addition, if you’re using a framework that works with a generic model like this one does, then centralizing the caching of data can make your application easier to maintain and scale.

If you find it's more flexible to implement the cache system within the controller, though, go ahead and do it. That will be fine too.

So far, so good. Now that the model class has been modified to cache data within its “fetchAll()” method, it’s necessary to evaluate the impact that this change has introduced to the controller. Will its definition remain exactly the same,  or will it need to be modified too?

The answer to these question will be revealed in the final section. So go ahead and read the following segment.