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 by "KutuKupret" about caching MySQL query results in memcached (Wayback Machine). 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 MySQL query results in your web server's RAM, utilizing PHP WinCache. This increases 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 while testing (MySQL 5.7 and older).

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 result in WinCache's user cache (ucache), and return the result. The PHP code is taken from KutuKupret's earlier mentioned blog post 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)
 * https://www.php.net/manual/en/function.wincache-ucache-add.php
 * 
 * 14-02-2013 - Jan Reilink, www.saotn.org Sysadmins of the North
 */

$dbhost = 'mysql.example.com';
$dbuser = 'examplecom';
$dbpass = 'pass_word';
$dbname = 'examplecom';
$conn = mysqli_connect( $dbhost, $dbuser, $dbpass, $dbname )
  or die ( 'Error connecting to mysql' );
 
$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 = mysqli_query( $conn, $query );
 
  $row = mysqli_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 );
 
  mysqli_free_result( $result );
}
?>

On first access, the query result is retrieved directly from the MySQL database and displayed in the browser:

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

Reload your browser, and now the query result is pulled from WinCache's memory and displayed in 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 the MySQL query result in memory, we get a nice performance gain! (:

Make sure WinCache is set up correctly first: here is how to run PHP with WinCache on IIS. And see the WinCache effect: save with object caching for what its object cache does for WordPress.

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

As someone correctly mentioned on Stack Overflow, 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 the database connection 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 = mysqli_connect( $dbhost, $dbuser, $dbpass, $dbname )
          or die ( 'Error connecting to mysql' );

  $query = "SELECT * FROM winc where FirstName='Memory';";
  $result = mysqli_query( $conn, $query );
 
  $row = mysqli_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 );
  mysqli_free_result( $result );
}
?>

PHP ext/mysql vs MySQLi functions

The original version of this article used the PHP ext/mysql functions (mysql_connect, mysql_query and so on) to communicate with the MySQL database. These functions were deprecated in PHP 5.5 and removed in PHP 7.0, so I updated the examples to MySQLi. If you have more old code to update, here is how to convert PHP ext/mysql to MySQLi. PDO is a good alternative too.