Sysadmins of the North
Don't forget to share this post!

MySQL query caching using PHP/Zend_Cache

Using the Cache.php PHP class of the Zend Framework, it is relatively easy to cache 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).

// 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:
	 * 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, 
	$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";


$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
// 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!

This may interest you:   Set WP_MEMORY_LIMIT value correctly in wp-config.php

use SQL_NO_CACHE in your query to test queries without MySQL’s own query-cache.

Flushing the cache
Call $cache->remove( $id ); to remove an entry from the cache.


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.

buy me a coffee
Buy Me A Coffee

About the Author Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link:

follow me on:

Leave a Comment:

1 comment
Add Your Reply
Skip to content