No! There is no error in the topic. You can see yourself:

andrey@winnie:~/dev/qcache/php5_3$ ./php -r '
> $flag=MYSQLI_CACHE_RESULT;
> $c=mysqli_connect("127.0.0.1","root","root");
> $t1=microtime(1);
> while ($i++ < 100000){
>   $r = $c->query("SELECT 1", MYSQLI_STORE_RESULT | $flag);
> }
> $t2=microtime(1);
> printf("Time %1.5f\n", $t2-$t1);'
Time 0.98548

And this is a debug PHP build.

We have announced that we work on a client side query cache for mysqlnd and it is there. Well, a better version that the last one I spoke about is ready. Ulf did some testing and we have a test file with a bunch of tests for this feature. More testing should come once Ulf is back from German PHP BBQ tour.

So, what do we offer? mysqlnd exposes the following INI settings:

• mysqlnd.qcache_max_results,        default:10,     PHP_INI_SYSTEM,    OnUpdateLong
• mysqlnd.qcache_max_set_size,    default:2048, PHP_INI_SYSTEM,    OnUpdateLong
• mysqlnd.qcache_TTL,  default:10,     PHP_INI_ALL,    OnUpdateLong
• mysqlnd.qcache_purge_occurance,    default:1000 , PHP_INI_ALL,    OnUpdateLong

So, one can restrict the number of results stored in the cache. This is a SYSTEM setting, meaning that cannot be changed withing a script.

qcache_max_set_size sets a limit on the size of the result set that can be cached. We calculate the size of the result set but the calculation is not 100% correct but pretty close to the real value. Selecting 1 column of int takes about 37 bytes, the metadata information is not counted.

qcache_TTL sets the life, in seconds, of a cached result. It can be changed within a script.

qcache_purge_occurances instructs mysqlnd how often it should check for stale data in the cache and delete it. Although it would have been the best to start a separate thread, which should sleep on a conditional variable and wake up when needed to clean the cache we did not do it. PHP has primitives for using mutexes, from TSRM, which are ok, but nothing more is there. Conditional are not there and Windows doesn't support POSIX. So, we did not took that way but the sequential one. Threading is a wonderful thing. I am the author of the Event Scheduler in MySQL 5.1 and it ticks in threads and with conditionals. Pretty :). There sill be chance, if the old libmysql 3.23 has included a version of my_pthread.h I can take the macros from there and use them (especially for Windows). However, this is a possibility, not something certain.

Every time you try to get data from the cache or put data in a counter, of the cache, is increased. When qcache_purge_occurance is reached the cache is checked for stale data. So, if it has value of 1000, then after 1000 get+put mysqlnd will clean the cache.

In addition, we excport through mysqli_refresh() a way to purge the cache.

mysqli_refresh($link, MYSQLI_REFRESH_CLIENT_CACHE);
This will purge the cache. The cache is purged also during MSHUTDOWN.

For now ext/mysql and ext/mysqli have separate caches which however use the same global variables. However, ext/mysql doesn't use its cache and we might remove it soon. It's there because mysqlnd_connect() needs a pointer and I haven't checked how it will work with NULL passed :)

The cache is thread-safe. So, if you use a threaded PHP then you might see a lot more speed-up as the result sets won't be spread amongst all the children, as it will be with prefork. For now we use a simple mutex to protect the access. A rw-lock probably seems better, however PHP doesn't have this primitive in TSRM (or I am not aware of it), thus it will need some coding to have it added.

"Where is the code?" you might ask. As this is an experimental feature we haven't committed it to PHP CVS. Another reason is because it does some pervasive changes to mysqlnd  It wouldn't have made sense to push it to HEAD and do the development there. Reviewing the feature's code is also simplified. The code resides in an internal bzr repository. If you want to try it please drop me an email and I will give you the code. I have a good feeling about the stability of the code but we needs Ulf's signature before we check it in PHP. However, don't forget it won't be part of PHP 5.3.0 which is in RC stage. Maybe it will be added later in the PHP 5_3 branch, along with iterators, which another feature we are working on, or in HEAD. I do prefer to have a single source, so it will even exist in 5.3 but won't be exposed, if we decide so.

Comments are disabled for this post.