When there is no MySQL query_cache available

Using the Cache.php PHP class of the Zend Framework, it is relatively easy to cache and save MySQL query results on disk. This speeds up execution. 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 in your cache text file, as long as the database hasn’t changed. Often this is easier 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: @HertogJanR

$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/migrate-php-mysql-mysqli/
	 * 
	 * For 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 witness a significant speed improvement of several tens to hundreds milliseconds. This is a relative big difference with little effort! You can use SQL_NO_CACHE in your query to test queries without MySQL’s own query-cache.

This may interest you:   PowerShell Get-FsrmQuota and Set-FsrmQuota

Flushing the cache
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.