How to cache a mysql_query using memcache?

Question

I would like to know if it's possible to store a "ressource" within memcache, I'm currently trying the following code but apparently it's not correct:

$result = mysql_query($sSQL);
$memcache->set($key, $result, 0, $ttl);
return $result;

Answer 1

I have to disagree with zerkms. Just because MySQL has a caching system (actually, it has several), doesn't mean that there's no benefit to optimizing your database access. MySQL's Query Cache is great, but it still has limitations:

• it's not suitable for large data sets
• queries have to be identical (character for character)
• it does not support prepared statements or queries using user-defined functions, temporary tables, or tables with column-level privileges
• cache results are cleared every time the table is modified, regardless of whether the result set is affected
• unless it resides on the same machine as the web server it still incurs unnecessary network overhead

Even with a remote server, Memcached is roughly 23% faster than MQC. And using APC's object cache, you can get up to a 990% improvement over using MQC alone.

So there are plenty of reasons to cache database result sets outside of MySQL's Query Cache. After all, you cache result data locally in a PHP variable when you need to access it multiple times in the same script. So why wouldn't you extend this across multiple requests if the result set doesn't change?

And just because the server is fast enough doesn't mean you shouldn't strive to write efficient code. It's not like it takes that much effort to cache database results—especially when accelerators like APC and Memcached were designed for this exact purpose. (And I wouldn't dismiss this question as such a "strange idea" when some of the largest sites on the internet use Memcached in conjunction with MySQL.)

That said, zerkms is correct in that you have to fetch the results first, then you can cache the data using APC or Memcached. There is however another option to caching query results manually, which is to use the Mysqlnd query result cache plugin. This is a client-side cache of MySQL query results.

The Mysqlnd query result cache plugin lets you transparently cache your queries using APC, Memcached, sqlite, or a user-specified data source. However, this plugin currently shares the same limitation as MQC in that prepared statements can't be cached.

Answer 2

Why do you need so? Mysql has its own performant query cache

but if you still want to follow your strange idea - you need to fetch all the data into array (with mysql_fetch_assoc or whatever) and after that store that array into the memcached.