Memcache - storing mysql results

Question

I have a database class, which manages all my mysql connections for my application.

I'm having to (belatedly) implement Memcache, for a number of reasons. I've started to figure it out but want to confirm one thing.

I was hoping to make amendments to class with defaults on timeout. It sees if it's in memcache, if not - make the query and store it.

However, it appears as though you can't store mysqli results (via this question). Does that mean you can't use any code in the application which says $db->fetch_assoc() or any other general object uses?

If so, does this mean we're talking ground up code change across the board? I want to confirm before I put my head in my hands.

Answer 1

You cannot store the result object, no, but you can fetch all rows into an array and store that array. If you need refactoring of your code in other places depends on how you have written your code and how well you have abstracted database access earlier.

For example, if you have a function like this:

function database_result($query) {
   ...
   $result_array = $result->fetchAll();
   return $result_array;
}

Then you can add Memcached caching inside that function:

function database_result($query, $expire = 60) {
   $memcached_key = 'db:' . $query;
   $cached = $memcached->get($memcached_key);
   if ($memcached->getResultCode() !== Memcached::RES_NOTFOUND) {
       return $cached;
   }
   ...
   $result_array = $result->fetchAll();
   $memcached->set($memcached_key, $result_array, $expire);
   return $result_array;
}

If you use the raw PDO or MySQLi object everywhere, then you have more work to do.

Answer 2

Memcache stores strings, not structures. You have to be able to serialize the structures out as strings to store them and and unserialize them back into structures to retrieve them.

This is simpler than it sounds, since PHP provides a set of functions to do this. See: http://php.net/manual/en/function.serialize.php

However, as a rule, when you're caching results, you always want to perform as much processing, filtering, and other manipulation on your results as you can BEFORE you cache them, that way when you retrieve the data from the cache, you don't have to always repeat the same processing steps immediately afterward.

Also bear in mind that MySQL already has a built-in query cache. If all you're doing is blindly storing query results based on the query string, then you're just duplicating functionality. If you want a significant performance boost, you need to "add value" somewhere, leveraging the knowledge you have about your application to cache application objects at a higher level than just database query results.