In November 2011, I wrote a post about MySQL query caching with PHP/Zend_Cache, and I recently stumbled upon a blog post caching MySQL query results in memcached by “KutuKupret”. This made me wonder if the same would be easily done with the Windows Cache Extension for PHP.

Cache MySQL query result sets in PHP with WinCache

Yes, and it turns out to be pretty easy as well! You can cache and store the MySQL query result in your web server RAM memory, utilizing PHP WinCache. This’ll  increase database and PHP performance.

Here is a simple PHP, WinCache and MySQL example.

Well, you almost can’t call this an example or demonstration, because I use only one record in the table. But here goes… Don’t forget to use SQL_NO_CACHE to disable MySQL’s own caching mechanism.

Setup a simple table in your MySQL database

CREATE TABLE winc (
 personID int NOT NULL AUTO_INCREMENT,
 PRIMARY KEY( personID ),
 FirstName varchar( 15 ),
 LastName varchar( 15 ),
 Age int);
INSERT INTO winc ( FirstName, LastName, Age )
VALUES ( 'Memory', 'Cache', '100' );

PHP script to cache MySQL query results in WinCache’s memory

Now create a simple PHP script to access the MySQL database, run the query, store the query in WinCache‘s ucache, and return the result. PHP code taken from KutuKupret his earlier mentioned blogpost and modified for this case.

All we have to do is generate a unique key for our query result and store that key (with its result of course) in the WinCache memory. We use an md5-hash for generating a unique key, wincache_ucache_add for adding the key/value to the WinCache memory and wincache_ucache_get to get a result if the key is found.

<?php
/**
 * WinCache Extension for PHP
 * store MySQL query result in Wincache user cache (ucache)
 * http://www.php.net/manual/en/function.wincache-ucache-add.php
 * 
 * 14-02-2013 - jan@saotn.nl, www.saotn.org Sysadmins of the North
 * Twitter: @HertogJanR
 * Donate: https://www.paypal.me/jreilink
 */

$dbhost = 'mysql.example.com';
$dbuser = 'examplecom';
$dbpass = 'pass_word';
$conn = mysql_connect( $dbhost, $dbuser, $dbpass )
	or die ( 'Error connecting to mysql' );
 
$dbname = 'examplecom';
mysql_select_db( $dbname );
 
$key = md5( "SELECT * FROM winc where FirstName='Memory'" );
$get_result = array();
$get_result = wincache_ucache_get( $key );
 
if ( $get_result ) {
        echo "FirstName: " . $get_result['FirstName'] . "\n";
        echo "LastName: " . $get_result['LastName'] . "\n";
        echo "Age: " . $get_result['Age'] . "\n";
        echo "Retrieved From Cache\n";
} else {
	// Run the query and get the data from the database then cache it
	// Disable MySQL Query Cache with SQL_NO_CACHE for testing!
        $query = "SELECT * FROM winc where FirstName='Memory';";
        $result = mysql_query( $query );
 
        $row = mysql_fetch_array( $result );
        echo "FirstName: " . $row[1] . "\n";
        echo "LastName: " . $row[2] . "\n";
        echo "Age: " . $row[3] . "\n";
        echo "Retrieved from the Database\n";

	// Store the result of the query for 30 seconds
        wincache_ucache_add( $key, $row, 30 );
 
        mysql_free_result( $result );
}

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

First time access, the query will be access directly from the MySQL database and displayed to the browser.

FirstName: Memory
LastName: Cache
Age: 100
Retrieved from the Database

Reload your browser, and now the query is pulled from WinCache’s memory and displayed to the browser.

FirstName: Memory
LastName: Cache
Age: 100
Retrieved From Cache

Timing test result

time wget -q -O - dev.example.org/dev/mysqlc.php
FirstName: Memory
LastName: Cache
Age: 100
Retrieved from the Database

real    0m0.222s
user    0m0.000s
sys     0m0.000s
time wget -q -O - dev.example.org/dev/mysqlc.php
FirstName: Memory
LastName: Cache
Age: 100
Retrieved From Cache

real    0m0.035s
user    0m0.000s
sys     0m0.000s

By storing MySQL query result in memory, we get a nice performance gain! (:

This may interest you:   How to restore a deleted Open-Xchange context?

PHP code optimization: only create a MySQL database connection when no cache entry is found

(updated 2013-10-13)

As someone correctly mentioned on stackoverflow, to further optimize the PHP code, it’s better to only make a database connection when no cache entry is found.

We do so by slightly altering the code and moving mysql_connect and mysql_select_db down:

<?php
$dbhost = 'mysql.example.com';
$dbuser = 'examplecom';
$dbpass = 'pass_word';
$dbname = 'examplecom';

$key = md5( "SELECT * FROM winc where FirstName='Memory'" );
$get_result = array();
$get_result = wincache_ucache_get( $key );
 
if ( $get_result ) {
	echo "FirstName: " . $get_result['FirstName'] . "\n";
	echo "LastName: " . $get_result['LastName'] . "\n";
	echo "Age: " . $get_result['Age'] . "\n";
	echo "Retrieved From Cache\n";
} else {
	// Make a database connection and run the query and get the data from 
	// the database. Then cache it
	// Disable MySQL Query Cache with SQL_NO_CACHE for testing!

	$conn = mysql_connect( $dbhost, $dbuser, $dbpass )
        	or die ( 'Error connecting to mysql' );
	mysql_select_db( $dbname );

        $query = "SELECT * FROM winc where FirstName='Memory';";
        $result = mysql_query( $query );
 
        $row = mysql_fetch_array($result);
        echo "FirstName: " . $row[1] . "\n";
        echo "LastName: " . $row[2] . "\n";
        echo "Age: " . $row[3] . "\n";
        echo "Retrieved from the Database\n";

	// Store the result of the query for 30 seconds
        wincache_ucache_add( $key, $row, 30 );
        mysql_free_result( $result );
}

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

PHP ext/mysql vs MySQLi functions

This article really needs an update, since it still uses the PHP ext/mysql functions to communicate with your MySQL database. These functions are deprecated. If you want to use these examples to store MySQL query results in WinCache’s memory, update the examples to MySQLi or PDO!

This may interest you:   List all MAC addresses of all Hyper-V Virtual Machines

Did this post help you solve a problem? Want to say thanks?

Did you find this post interesting? Did it help you solve a problem? If I’ve helped you out and you want to thank me, then why not buy me a coffee?

A small donation of only $5 helps out a lot in the development, research and hosting of this blog.

If I’ve helped you out and you want to thank me, why not buy me a coffee?

Thank you for your support.