Caching mysql result with memcache and php

There are many ways to boost up the performance of the website like css amalgamation with php, use of plugins, caching in your php scripts, optimize your images, database etc.

I am going to share one of the method i.e memcache which help us to optimize load on the database. Normally a request for a dynamic page triggers multiple db queries, processing of output, and finally formatting to display on browser. This process can eventually be slow on larger sites or slower servers.

Caching of output in PHP is made easier by the use of the output buffering functions built in to PHP 4 and above.

Instead of loading the data from the database every time it will trigger the database once and store it in the memcache. The second time when the user refreshes the page the data will be retrieved from the cache itself rather than the database. It will help us in eliminating the load on the database and thus boosting up the performance of the website.

The following post is about the “How to store the data in the memcache in order to retrieve the data from it”.

Few steps have to be taken care of before using this post.

  1. Memcache should be installed on your system.
  2. Create a table with mysql or phpmyadmin from which you will retrieve the data.
  3. Here it goes:
// Connect into database
// Change the DB configuration according to you

define("HOST", "xxxxx");
define("USER", "xxxxx");
define("PASS", "xxxxx");
define("DBNAME", "xxxxx");

$conn = mysql_connect(HOST, USER, PASS);

if($conn){
mysql_select_db(DBNAME, $conn);
}
else{
die('Error in Connection!');
}

$memc = new Memcache;
$memc->connect('127.0.0.1', 11211) or die('Error in Connection!');

//Set the table name according to Your Table
$key = md5("select * from tbl_getDetail");
$get_result = array();

$get_result = $memc->get($key);
if($get_result){
echo 'From Memory:<br><br>';
foreach( $get_result as $val ){
echo 'Name: ' . $val . '<br>';
}
}else{

//Set the table name according to Your Table
$query = "select * from tbl_getDetail";
$result = mysql_query($query) or die('Error in Query!');

echo 'From Database:<br><br>';

$getAllResult = array();
while( $row = mysql_fetch_array($result) ){

//Please set the field name according to your Table
echo 'Name: ' . $row['name'] . '<br>';
$getAllResult[] = $row['name'];
}

//Set Data In Cache for 20 Sec. or set 0 to store lifetime
$memc->set($key, $getAllResult, MEMCACHE_COMPRESSED, 20);
mysql_free_result($result);
}