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
);
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: @Jan_Reilink
* 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! (:
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!