Memcached And PHP, Caching Mysql Query Result

I’ve been messing around with memcached and php-pecl-memcache to cache sql query result. Many web sites & applications such as Facebook, LiveJournal, Flickr, Slashdot, WikiPedia/MediaWiki, SourceForge, Digg and Twitter use memcached to enhance their performance.

Memcached (Memory Cache Daemon) was developed by the team at LiveJournal to improve performance of their social blogging site by minimizing the impact of the bottleneck caused by reading data directly from the database. Memcached is a server that caches Name Value Pairs in memory. The “Name”, or key, is limited to 250 characters, and the “Value” is limited to 1MB in size. Values can consist of data, HTML  Fragments, or binary objects; almost any type of data that can be serialized and fits in memcached can be stored.

here is simple example/demonstration how to cache regular sql query

First of all, we need memcached daemon run on system

$ ps ax | grep memcached
 8955 ?        Ssl    0:00 memcached -d -p 11211 -u memcached -m 256 -c 1024 -P /var/run/memcached/memcached.pid -l 127.0.0.1

Setup simple mysql database/tables as shown bellow:

mysql-shell> CREATE DATABASE memcache;

Copy/Paste this tables schema to your mysql shell/console

CREATE TABLE memc
(
 personID int NOT NULL AUTO_INCREMENT,
 PRIMARY KEY(personID),
 FirstName varchar(15),
 LastName varchar(15),
 Age int
);
hit enter/return key

Insert some data

mysql-shell> INSERT INTO memc (FirstName, LastName, Age) VALUES('Memory', 'Cache', '100');

In http document root put these php codes (you can create subdirectory)
db.php

<?php
 $dbhost = '127.0.0.1';
 $dbuser = 'user';
 $dbpass = 'password';
 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

 $dbname = 'memcache';
 mysql_select_db($dbname);
?>

mc.php

<?php
$memcache = new Memcache;
$memcache->connect('127.0.0.1', 11211) or die ("Could not connect");

include('./db.php');

$key = md5("SELECT * FROM memc where FirstName='Memory'");
$get_result = array();
$get_result = $memcache->get($key);

if ($get_result) {
        echo "<pre>\n";
        echo "FirstName: " . $get_result['FirstName'] . "\n";
        echo "LastName: " . $get_result['LastName'] . "\n";
        echo "Age: " . $get_result['Age'] . "\n";
        echo "Retrieved From Cache\n";
        echo "</pre>\n";
} else {
        // Run the query and get the data from the database then cache it
        $query="SELECT * FROM memc where FirstName='Memory';";
        $result = mysql_query($query);

        $row = mysql_fetch_array($result);
        echo "<pre>\n";
        echo "FirstName: " . $row[1] . "\n";
        echo "LastName: " . $row[2] . "\n";
        echo "Age: " . $row[3] . "\n";
        echo "Retrieved from the Database\n";
        echo "</pre>\n";
        $memcache->set($key, $row, MEMCACHE_COMPRESSED, 20); // Store the result of the query for 20 seconds

        mysql_free_result($result);
}

?>

When all done, point your browser to http://www.example.com/mc.php

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

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

Reload the browser, now query will be pulled from memcached and displayed to the browser.

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

Timing test result

# time lynx -dump http://www.example.com/mc.php
FirstName: Memory
LastName: Cache
Age: 100
Retrieved from the Database

real    0m0.286s
user    0m0.018s
sys     0m0.022s

Second hit was from cache

# time lynx -dump http://www.example.com/mc.php
FirstName: Memory
LastName: Cache
Age: 100
Retrieved From Cache

real    0m0.050s
user    0m0.013s
sys     0m0.014s

not bad
happy memcaching 🙂