MySQL query caching using PHP/Zend_Cache

By using the Cache.php PHP class of the Zend Framework, you can relatively easily cache MySQL query results to disk. In this context, caching means: execute a MySQL query once and save the result in a temporary file. 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 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 query results with PHP WinCache for a how-to. This is an older, republished post, supporting that article.

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 an integer as lifetime value. For example 3600 (seconds).

<?php
$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:
   * https://www.saotn.org/codebase/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.';
?>

If implemented correctly you might see a significant speed boost of several tens to hundreds of milliseconds. This is a relatively 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.

Caching is not the only way to speed up your queries. Read about MySQL database optimization with indices.