Cache MySQL query results with PHP WinCache

Photo of author
Written By Jan Reilink

Windows Server systems administrator & enthusiast.

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.

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. Or another PHP opcode cache like Opcache for that matter. 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 );
Code language: PHP (php)
INSERT INTO winc ( FirstName, LastName, Age ) VALUES ( 'Memory', 'Cache', '100' );
Code language: JavaScript (javascript)

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 $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 { $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"; wincache_ucache_add( $key, $row, 30 ); mysql_free_result( $result ); } ?>
Code language: PHP (php)

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
Code language: JavaScript (javascript)

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
Code language: JavaScript (javascript)
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! (:

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 { $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"; wincache_ucache_add( $key, $row, 30 ); mysql_free_result( $result ); } ?>
Code language: PHP (php)

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!


Did you like: Cache MySQL query results with PHP WinCache

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.



4 thoughts on “Cache MySQL query results with PHP WinCache”

  1. I would like to know that how long cached results will be used. What happens if something gets changed in database when data is being cached? If we want updated results from db how db can update cache whenever its values changes?

    Reply
  2. (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 {
    
    
            $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";
    	}
    
    
            wincache_ucache_add($key, $row, 30);
     
            mysql_free_result($result);
    }
    Reply

Hi! Join the discussion, leave a reply!

%d bloggers like this: