Sysadmins of the North

Technical blog, where topics include: computer, server, web, sysadmin, MySQL, database, virtualization, optimization and security

Cache MySQL query results with PHP WinCache

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.

This may interest you:   MySQL query caching using PHP/Zend_Cache

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
 */

$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 string replace on all WordPress posts in MySQL

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!

2 Comments

  1. (Please disregard my previous comment. It had a few errors.)

    Hi Jan,

    Thank you very much for this very useful guide! How would this need to be changed in order to cache an array of multiple rows? i.e. This is as close as I could get and it doesn’t seem to be working:

    key = md5("SELECT FirstName FROM winc LIMIT 2");
    $get_result = array();
    $get_result = wincache_ucache_get($key);
    
    if ($get_result) {
            while ($item = mysql_fetch_array($get_result)){
                    echo "FirstName: " . $item['FirstName'] . "\n";
                    echo "LastName: " . $item['LastName'] . "\n";
                    echo "Age: " . $item['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 FirstName FROM winc LIMIT 2;";
            $result = mysql_query($query);
     
            while ($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);
    }

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.