MySQL query caching using PHP/Zend_Cache

Photo of author
Written By Jan Reilink

Windows Server system administrator & enthusiast.

By using the Cache.php PHP class of the Zend Framework, you can relatively easy cache MySQL query results to disk. This ensures faster consecutive results and 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 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 $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 ); $backendOptions = array( 'cache_dir' => '../../temp' ); $cache = Zend_Cache::factory( 'Core', 'File', $frontendOptions, $backendOptions ); $query = "SELECT SQL_NO_CACHE * FROM `wp_smf_log_comments`"; $id = sha1( $query ); if( !( $data = $cache->load( $id ) ) ) { echo "Not found in Cache<br>"; $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.'; ?>
Code language: PHP (php)

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.


Did you like: MySQL query caching using PHP/Zend_Cache

Then please, take a second to support Sysadmins of the North and donate!

Your generosity helps pay for the ongoing costs associated with running this website like coffee, hosting services, library mirrors, domain renewals, time for article research, and coffee, just to name a few.



Hi! Join the discussion, leave a reply!