PHP Database Access Caching Queries and Results

PHP Database Access

Caching Queries and Results

Problem

You don’t want to rerun potentially expensive database queries when the results haven’t changed.

Solution

Use PEAR’s Cache_Lite package. It makes it simple to cache arbitrary data. In this case, cache the results of a SELECT query and use the text of the query as a cache key.

Example  Caching query results

          require_once 'Cache/Lite.php';

          $opts = array(

                 // Where to put the cached data

                 'cacheDir' => '/tmp/',

                 // Let us store arrays in the cache

                'automaticSerialization' => true,

                // How long stuff lives in the cache

                'lifeTime' => 600 /* ten minutes */);

          // Create the cache

          $cache = new Cache_Lite($opts);

          // Connect to the database

          $db = new PDO('sqlite:/tmp/zodiac.db');

          // Define our query and its parameters

          $sql = 'SELECT * FROM zodiac WHERE planet = ?';

          $params = array($_GET['planet']);

          // Get the unique cache key

          $key = cache_key($sql, $params);

          // Try to get results from the cache

          $results = $cache->get($key);

           if ($results === false) {

                 // No results found, so do the query and put the results in the cache

                 $st = $db->prepare($sql);

                 $st->execute($params);

                 $results = $st->fetchAll();

                 $cache->save($results);

           }

           // Whether from the cache or not, $results has our data

           foreach ($results as $result) {

                 print "$result[id]: $result[planet], $result[sign] <br/>\n";

           }

           function cache_key($sql, $params) {

                 return md5($sql .

                                        implode('|',array_keys($params)) .

                                        implode('|',$params));

           }

Discussion

Cache_Lite is a generic, lightweight mechanism for caching arbitrary information. It uses files to store the information it’s caching. The Cache_Lite constructor takes an array of options that control its behavior. The two most important ones are automaticSerialization, which makes it easier to store arrays in the cache, and cacheDir, which defines where the cache files go. Make sure cacheDir ends with a /.

The cache is just a mapping of keys to values. It’s up to us to make sure that we supply a cache key that uniquely identifies the data we want to cache—in this case, the SQL query and the parameters bound to it. The cache_key function computes an appropriate key. After that, just checks to see if the results are already in the cache.If not, it executes the query against the database and stuffs the results in the cache for next time.

Note that you can’t put a PDO or PDOStatement object in the cache—you have to fetch results and then put the results in the cache.

By default, entries stay in the cache for one hour. You can adjust this by passing a different value (in seconds) as the lifeTime option when creating a new Cache_Lite object. Pass in null if you don’t want data to automatically expire.

The cache isn’t altered if you change the database with an INSERT, UPDATE, or DELETE query. If there are cached SELECT statements that refer to data no longer in the database, you need to explicitly remove everything from the cache with the Cache_Lite::clean() method. You can also remove an individual element from the cache by passing a cache key to Cache_Lite::remove().

The cache_key() function is case sensitive. This means that if the results of SELECT * FROM zodiac are in the cache, and you run the query SELECT * from zodiac, the results aren’t found in the cache and the query is run again. Maintaining consistent capitalization, spacing, and field ordering when constructing your SQL queries results in more efficient cache usage.

A benefit of PHP-layer solutions such as Cache_Lite is that they are database agnostic. However, depending on the database you’re using you may be able to take advantage of database-specific query caching mechanisms. These kinds of caches, because they are more tightly integrated into the database, are able to be smarter about expiring cached data when it has changed. For example, you can read about how to enable MySQL’s query cache.