How do you use memcached with Prepared Statements?

Question

Caching regular SQL queries is simple enough.

public function query($sql) {

    if( $result = cache::get(sha1($sql)) ) {
     return $result;
    }

    $result = $this->connection->query($sql);
    cache::set(sha1($sql), $result);
    return $result;
}

But how do you cache queries with prepared statements since you don't know what the query will be until after the statement is already prepared and then the data is bound?

$sth = $dbh->prepare('SELECT * FROM table WHERE id = ?');

...later...

$sth->bindParam(1, $id);
$sth->execute();

I get the feeling that this is a two-part answer: First, the statements are cached in per-page memory (like an array of $this->statements[]) since the database resource ids won't last long and can't be stored in files or anything.

Second, before the statements are executed() we look in memcached/filecache for the results by hashing the sql used to create the statement (easy with PDOStatement::queryString) plus the hashes of the params given. The trouble is finding the params in the statement object.

Of course, this is just one idea and there are probably better solutions.

Answer 1

Well, you have to add the value of each of your params to your cache key. Something like this:

public function stmt($sql, $params) {

    $cache_key = sha1($sql . serialize($params));

    if( $result = cache::get($cache_key) ) {
        return $result;
    }

    $sth = $this->connection->prepare($sql);

    $i = 0;
    foreach ($params as &$param)
    {
        $sth->bindParam(++$i, $param);
        $sth->execute();
    }
    unset($param)

    // fetch all the rows into $result

    cache::set($cache_key, $result);
    return $result;
}

$obj->stmt('SELECT * FROM table WHERE id = ?', array(&$id));

I'll leave it to you to adapt it to your needs. You'll have to fetch the rows and store them in an array.

---

Here's the kind of wrapper you'd have to use:

class stmt
{
    protected $sth, $sql, $cache, $params = array();

    public function __construct($dbh, $sql)
    {
        $this->sth = $dbh->prepare($sql);
        $this->sql = $sql;
    }

    public function bindParam($param, &$var)
    {
        $this->params[$param] =& $var;
        return $this->sth->bindParam($param, $var);

        // or, if you want to support all the args
        $args = func_get_args();
        $args[1] =& $var;

        return call_user_func_array(array($this->sth, 'bindParam'), $args);
    }

    public function execute(array $params = null)
    {
        $str = serialize(isset($params) ? $params : $this->params);
        $cache_key = sha1($this->sql . $str);

        // insert cache logic here...

        if (isset($params))
        {
            $this->stmt->execute($params);
        }
        else
        {
            $this->stmt->execute();
        }

        $this->cache = $this->stmt->fetchAll();

        // save cache here
    }

    public function fetch()
    {
        return array_shift($this->cache);
    }
}

You'd have to match every PDOStatement method you plan to use. PDO::FETCH_INTO would be kind of a pain to implement, too. My advice: focus on your own usage. Perhaps you don't even have to implement a cache at the dbh level and instead you can add caching capabilities only where it counts.

At any rate, keep in mind that the more code you write, the more code you'll have to maintain and the more likely it is you will introduce bugs in your application. So be careful with the cost/benefits analysis of a caching layer that would try to be too smart for its own good :)