Issue Caching Resultsets using PHP and APC on Windows

Question

Hi,

Just for the record, I'm using Windows Vista with XAMPP (PHP 5.3.1).

I'm trying to use APC to cache a database result. I did a simple APC test on string variables and it seems to work ok. However, when I try to do the same thing with a database result resource, I get a complaint the data in the cache 'is not a valid MySQL result resource' whenever I want to use it.

Here is my code:

$key_hash_str = md5($query_sql_str);
$cache_res = Mox_Cache_APC::fetch($key_hash_str);;

switch(true)
{
case (!$cache_res):
    $query_result_res = self::executeQuery($query_sql_str);
    Mox_Cache_APC::store($key_hash_str, $query_result_res);
    return $query_result_res;                   
break;

default:
    return $cache_res;
}

Mox_Cache_APC is my APC class and fetch and store are just abstractions for apc_fetch() and apc_store(). executeQuery is a static function defined within the class were this code is written (as is evident, for executing the Query).

Am I doing something wrong? Is there something I need to do to the resultset before caching it?

Kindly advise.

Answer 1

The short answer: You can't store the raw result resource. You could fetch it into a native array (using something like mysql_fetch_assoc()) and then store it, but not the resource.

However, why not just enable MySQL's query caching? It does the exact same thing as this. Sure, it requires an additional TCP round trip to the db server, but it will automatically prune stale cache results from the db when you insert/update/delete rows from a table... Unless you're running hundreds of queries on the same page, it should be about the same performance wise...

Answer 2

That's to be expected. You cannot cache database resources.

In fact, you cannot cache any other PHP resources. PHP resources are a special type. The variable itself holds only an identifier and the type of resource. That identifier can be used by the functions that accept that type of resource to fetch its data and manipulate it; however, PHP itself doesn't know anything about that value (including how big it is and whether it depends on other resources).

There's also no general mechanism for serialization which can be implemented by the extension that provides the resource, like there is for objects. Consequently, it's not possible to cache resources.

If you want, you can cache the results in array form.