Using Memcache with MySQL and PHP

Posted on May 21, 2008. Filed under: Memcache, mysql+, PHP | Tags: , , , , , |

Memcache is a great caching tool available for nearly every scripting or programming environment. I use it with PHP to speed up some applications I have written by completely avoiding asking the database for information. I wanted a very clean way of implementing this in my various projects, with as little change to the existing code as possible. Since memcache is an OBJECT caching system, you can’t simply drop your mysql_query resource into memcache, ask for it at another time, and get your results back … Which is how I had originally thought it worked the first time I read about it. The good news is that it can also store Strings, Integers, Floats, Arrays etc … Below I have a few other things I’ve learned whilst using memcache as well as the caching script I use for database queries.

What I’ve learned:

  1. memcache is great for storing slow queries that return small data sets [1 – 50 results, depending on the average row weight]
  2. memcache is not so great for any query that returns large data sets [100 – ∞, depending on the average row weight]

… in fact, I’ve found that memcache can occasionally be slower than running high-yield queries again [that is, if you have your MySQL server caching queries as well]. It all really boils down to benchmarking the scripts yourself: test every situation with and without the cache! [the more realistic the DB load, the better]

Caching is faster? Yay! Cache it!
DB query is faster? Yay! DON’T cache it!

So, basically, this isn’t a plug-and-play solution for ALL slow page loads / queries, just some of them.

Here’s the code I use to cache MySQL queries:

    # Connect to memcache:
    global $memcache;
    $memcache = new Memcache;

    # Gets key / value pair into memcache ... called by mysql_query_cache()
    function getCache($key) {
        global $memcache;
        return ($memcache) ? $memcache->get($key) : false;

    # Puts key / value pair into memcache ... called by mysql_query_cache()
    function setCache($key,$object,$timeout = 60) {
        global $memcache;
        return ($memcache) ? $memcache->set($key,$object,MEMCACHE_COMPRESSED,$timeout) : false;

    # Caching version of mysql_query()
    function mysql_query_cache($sql,$linkIdentifier = false,$timeout = 60) {
        if (($cache = getCache(md5("mysql_query" . $sql))) !== false) {
            $cache = false;
            $r = ($linkIdentifier !== false) ? mysql_query($sql,$linkIdentifier) : mysql_query($sql);
            if (is_resource($r) && (($rows = mysql_num_rows($r)) !== 0)) {
                for ($i=0;$i<$rows;$i++) {
                    $fields = mysql_num_fields($r);
                    $row = mysql_fetch_array($r);
                    for ($j=0;$j<$fields;$j++) {
                        if ($i === 0) {
                            $columns[$j] = mysql_field_name($r,$j);
                        $cache[$i][$columns[$j]] = $row[$j];
                if (!setCache(md5("mysql_query" . $sql),$cache,$timeout)) {
                    # If we get here, there isn't a memcache daemon running or responding
        return $cache;

The function mysql_query_cache() will return an array filled with the results. Since I don’t use this for large result sets, I don’t free the MySQL resource … you may want to free the resource after it’s been used if you get larger data sets.

Like I had mentioned before, I wanted the code to be as easy-to-use as possible when using it. So, I’ve set up a before and after test scenario showing how to retrofit your code with the new caching code:

    $sql = "
        SELECT `dataID`, `dataTitle`
        FROM `tbldata`
        WHERE `dataTypeID` BETWEEN 2 AND 2093
        AND `dataStatusID` IN (1,2,3,4)
        AND `dataTitle` LIKE '%something%'
        ORDER BY `dataDate` DESC
        LIMIT 10

    # Before: [without memcache]
    $rSlowQuery = mysql_query($sql);
    # $rSlowQuery is a MySQL resource
    $rows = mysql_num_rows($rSlowQuery);
    for ($i=0;$i<$rows;$i++) {
        $dataID = intval(mysql_result($rSlowQuery,$i,"dataID"));
        $dataTitle = mysql_result($rSlowQuery,$i,"dataTitle");

        echo "<a href=\"/somewhere/$dataID\">$dataTitle</a><br />\n";

    # After: [with memcache]
    $rSlowQuery = mysql_query_cache($sql);
    # $rSlowQuery is an array
    $rows = count($rSlowQuery);
    for ($i=0;$i<$rows;$i++) {
        $dataID = intval($rSlowQuery[$i]["dataID"]);
        $dataTitle = $rSlowQuery[$i]["dataTitle"];

        echo "<a href=\"/somewhere/$dataID\">$dataTitle</a><br />\n";


Easy, huh? Run print_r() on the returned array to get an idea of how the array is structured if need be.

As always, if you have a better, more efficient, objective, more adaptable solution than mine, please leave a comment! I am 100% open to constructive criticism :-)

Read Full Post | Make a Comment ( 67 so far )

Installing memcache on Windows for PHP

Posted on January 10, 2008. Filed under: Apache, Memcache, PHP | Tags: , , , , , |


Installing memcache on Windows XP / Vista is kind of like voodoo for those of us who are not disciplined with compiling code from source. I initially attempted to install memcache a few months ago after reading a few articles about how much performance it can pump into your web application. The problem is that memcache was written with Linux in mind, not windows. So you can’t download any installers or exe files from memcache’s site for windows … which leaves people like me, who use WAMP stacks to develop applications, out in the cold.

So after a few hours of Googling I found a cocktail of methods and files to get memcache to work for win32.

A few things about memcache:
Memcache is a daemon, meaning it runs as a separate service on your machine. Just like MySQL runs as a separate service. In fact, to use memcache in PHP you have to connect to it, just like MySQL.

Think of memcache as the $_SESSION variable for PHP, but instead of it working on a per-user basis, it runs over the entire application — like MySQL. In fact, you can use memcache as you session handler for PHP.


This is how I got memcache to work on my windows machine:

  1. Download memcache from [grab the ‘win32 binary’ version]
  2. Install memcache as a service:
    • Unzip and copy the binaries to your desired directory (eg. c:\memcached) [you should see one file, memcached.exe] – thanks to Stephen for the heads up on the new version
    • If you’re running Vista, right click on memcached.exe and click Properties. Click the Compatibility tab. Near the bottom you’ll see Privilege Level, check “Run this program as an administrator”.
    • Install the service using the command: c:\memcached\memcached.exe -d install from the command line
    • Start the server from the Microsoft Management Console or by running one of the following commands: c:\memcached\memcached.exe -d start, or net start "memcached Server"


Now that you have memcache installed, you’ll have to tie it in with PHP in order to use it.

  1. Check your php extensions directory [should be something like: C:\php\ext] for php_memcache.dll
    If you don’t have any luck finding it, try looking at one of these sites: [thanks to Henrik Gemal] [currently down] for PHP 5.2.* for PHP 5.1.* [thanks, Rich]
  2. Now find your php.ini file [default location for XP Pro is C:\WINDOWS\php.ini] and add this line to the extensions list:
  3. Restart apache
  4. Run this code to test the installation: [found on]
        $memcache = new Memcache;
        $memcache->connect("localhost",11211); # You might need to set "localhost" to ""
        echo "Server's version: " . $memcache->getVersion() . "<br />\n";
        $tmp_object = new stdClass;
        $tmp_object->str_attr = "test";
        $tmp_object->int_attr = 123;
        echo "Store data in the cache (data will expire in 10 seconds)<br />\n";
        echo "Data from the cache:<br />\n";

If you see anything but errors, you are now using memcache!


Memcached, by default, loads with 64mb of memory for it’s use which is low for most applications. To change this to something else, navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\memcached Server in your registry, find the ImagePath entry and change it to look something like this:

“C:\memcached\memcached.exe” -d runservice -m 512

Now when you start the service via net start “memcached Server”, it will run with 512mb of memory at it’s disposal.


[Thanks to Travis]
If anyone is wondering what other options can be set (other than the memory limit), run “memcached -help” in a command prompt window. Then modify the ImagePath command line per this article with the desired switches and values.

Read Full Post | Make a Comment ( 185 so far )

Liked it here?
Why not try sites on the blogroll...