MySQL query caching using PHP/Zend_Cache

In this context, caching means: execute a MySQL query once and save the result in a temporary file. For every time that same query is executed, you can simply use the result stored in your disk cache file, as long as the database hasn't changed. Often it is faster to save this on the web server than having it to redo the query every time.

Did you know you can cache MySQL query results with PHP WinCache too, as an alternative to PHP/Zend_Cache? See my article cache MySQL queries in PHP with WinCache for a how to. Please note that this is an older, republished post, supporting that article about caching query results with WinCache.

PHP Zend_Cache code example to cache MySQL query results

As said, using Zend's Cache.php class it's relatively easy to cache MySQL query results to disk on your web server.

In this example I use a null value as lifetime. Meaning the cache never expires. In a real-world example you have to invalidate the cache from time to time, so give in an integer as lifetime value. For example 3600 (seconds).

<?php
// follow me on Twitter: @Jan_Reilink

$stime = gettimeofday();
require_once 'Zend/Cache.php';
define('DB_NAME', 'db_name');
define('DB_USER', 'db_user');
define('DB_PASSWORD', 'passw0rd');
define('DB_HOST', 'db_host');
  
$frontendOptions = array(
    'lifetime' => null,
    'automatic_serialization' => true
);

/**
 * fill out a correct path for your cache
 * files, preferably outside your webroot
 **/
$backendOptions = array( 'cache_dir' => '../../temp' );
$cache = Zend_Cache::factory( 'Core', 'File', $frontendOptions, $backendOptions );

$query = "SELECT SQL_NO_CACHE * FROM `wp_smf_log_comments`";

// sha1 hash the query to use as unique cache identifier
$id = sha1( $query );
if( !( $data = $cache->load( $id ) ) ) {
  echo "Not found in Cache<br>";
  /**
   * Protip: learn to Convert PHP ext/mysql to MySQLi:
   * /posts/migrate-php-mysql-mysqli/
   * 
   * For secure SSL connections to MySQL, use:
   * $db = mysqli_init();
   * mysqli_options ( $db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true );
   * $link = mysqli_real_connect ( $db, DB_HOST, DB_USER, DB_PASSWORD, 
   *     DB_NAME, 3306, NULL, MYSQLI_CLIENT_SSL );
   **/
  $link = mysqli_connect( gethostbyname( DB_HOST ),  DB_USER, DB_PASSWORD, DB_NAME );
  $rs = mysqli_query( $link, $query );
  $data = array();
  while( $row = mysqli_fetch_assoc( $rs ) ) {
    $data[] = $row;
  }

    $cache->save( $data );
}
else {
    echo "Running from Cache";
}

print_r($data);

$ftime = gettimeofday();
$time = round( ( $ftime['sec'] + $ftime['usec'] / 1000000 ) +- ( $stime['sec'] + $stime['usec'] / 1000000 ), 5 );

echo 'Generated in '.$time.' s.';

// Did you like this code or article? Then please donation to
// https://paypal.me/jreilink. It'll be used for coffee, thanks! :)
?>

If implemented correctly you might see a significant speed boost of several tens to hundreds milliseconds. This is a relative big difference with little effort! Use SQL_NO_CACHE in your query to test queries without MySQL's own query-cache.

Flushing the cache

Simply call $cache->remove($id); to remove an entry from the cache.

Caveats

Important to know: this is old PHP code. It relies on Zend Framework version 1.12.20. This post is (re)made as a support article to the post Cache MySQL query results with PHP WinCache.