May 042011

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

memcached flow

memcached flow

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

Setup simple mysql database/tables as shown bellow:

mysql-shell> CREATE DATABASE memcache;

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

 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)

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

 $dbname = 'memcache';


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


$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



When all done, point your browser to

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
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
FirstName: Memory
LastName: Cache
Age: 100
Retrieved From Cache

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

not bad
happy memcaching 🙂

  7 Responses to “Memcached And PHP, Caching Mysql Query Result”

Comments (7)
  1. hi,this code run proper but the data fetch from database every time not from catch

  2. are you expiring your memcahced database?

  3. what exactly is the path for saving the php files?

    i am not able to figure that out.

    pls help urgent

  4. ahh, yes, you can save it in your www docroot, or in any subdirectory on docroot, sorry for misleading path information.

  5. Great article, thanks!
    I used your idea and example -hope you don’t mind- for my own test and article with PHP and Windows Cache Extension (WinCache) to store MySQL query results in WinCache’s memory.

  6. sure, be my guest 🙂

  7. Thanks for your the article. It’s great.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>