Memcached And PHP, Caching Mysql Query Result – KutuKupret colorful website

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

`1`	`$` `ps` `ax \|` `grep` `memcached`

`2`	`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:

`1`	`mysql-shell> CREATE DATABASE memcache;`

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

1CREATE TABLE memc
2(
3 personID int NOT NULL AUTO_INCREMENT,
4 PRIMARY KEY(personID),
5 FirstName varchar(15),
6 LastName varchar(15),
7 Age int
8);
9hit enter/return key

Insert some data

`1`	`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

1<?php
2 $dbhost = '127.0.0.1';
3 $dbuser = 'user';
4 $dbpass = 'password';
5 $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
6 
7 $dbname = 'memcache';
8 mysql_select_db($dbname);
9?>

mc.php

1<?php
2$memcache = new Memcache;
3$memcache->connect('127.0.0.1', 11211) or die ("Could not connect");
4 
5include('./db.php');
6 
7$key = md5("SELECT * FROM memc where FirstName='Memory'");
8$get_result = array();
9$get_result = $memcache->get($key);
10 
11if ($get_result) {
12        echo "<pre>\n";
13        echo "FirstName: " . $get_result['FirstName'] . "\n";
14        echo "LastName: " . $get_result['LastName'] . "\n";
15        echo "Age: " . $get_result['Age'] . "\n";
16        echo "Retrieved From Cache\n";
17        echo "</pre>\n";
18} else {
19        // Run the query and get the data from the database then cache it
20        $query="SELECT * FROM memc where FirstName='Memory';";
21        $result = mysql_query($query);
22 
23        $row = mysql_fetch_array($result);
24        echo "<pre>\n";
25        echo "FirstName: " . $row[1] . "\n";
26        echo "LastName: " . $row[2] . "\n";
27        echo "Age: " . $row[3] . "\n";
28        echo "Retrieved from the Database\n";
29        echo "</pre>\n";
30        $memcache->set($key, $row, MEMCACHE_COMPRESSED, 20); // Store the result of the query for 20 seconds
31 
32        mysql_free_result($result);
33}
34 
35?>

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.

1FirstName: Memory
2LastName: Cache
3Age: 100
4Retrieved from the Database

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

1FirstName: Memory
2LastName: Cache
3Age: 100
4Retrieved From Cache

Timing test result

1# time lynx -dump http://www.example.com/mc.php
2FirstName: Memory
3LastName: Cache
4Age: 100
5Retrieved from the Database
6 
7real    0m0.286s
8user    0m0.018s
9sys     0m0.022s

Second hit was from cache

1# time lynx -dump http://www.example.com/mc.php
2FirstName: Memory
3LastName: Cache
4Age: 100
5Retrieved From Cache
6 
7real    0m0.050s
8user    0m0.013s
9sys     0m0.014s

not bad
happy memcaching

7 Comments

MAngesh Pardhi


April 24, 2012, 1:05 pm

hi,this code run proper but the data fetch from database every time not from catch
- leenoux
  
  
  April 24, 2012, 3:41 pm
  
  are you expiring your memcahced database?
ankit


September 14, 2012, 4:55 am

what exactly is the path for saving the php files?

i am not able to figure that out.

pls help urgent
- leenoux
  
  
  September 14, 2012, 7:12 am
  
  ahh, yes, you can save it in your www docroot, or in any subdirectory on docroot, sorry for misleading path information.
Jan Reilink


February 14, 2013, 5:48 pm

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.
- leenoux
  
  
  February 14, 2013, 7:02 pm
  
  sure, be my guest
Nguyen Binh


March 29, 2013, 4:52 pm

Thanks for your the article. It’s great.

7 Comments

Leave a Reply to Jan Reilink Cancel reply