Adventures in PHP / DHTML / CSS and MySQL

Using Memcache with MySQL and PHP

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

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:

<?php
    # 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:

<?php
    $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 :-)

Make a Comment

75 Responses to “Using Memcache with MySQL and PHP”

Comments RSS Feed

Where's The Comment Form?