Copyright 1997-2018 the PHP Documentation Group.
An application has three options for telling PECL/mysqlnd_qc
whether a particular statement shall be used. The most basic
approach is to cache all statements by setting
mysqlnd_qc.cache_by_default = 1
. This approach
is often of little practical value. But it enables users to make
a quick estimation about the maximum performance gains from
caching. An application designed to use a cache may be able to
prefix selected statements with the appropriate SQL hints.
However, altering an applications source code may not always be
possible or desired, for example, to avoid problems with
software updates. Therefore, PECL/mysqlnd_qc allows setting a
callback which decides if a query is to be cached.
The callback is installed with the
mysqlnd_qc_set_is_select
function. The callback is given the statement string of every
statement inspected by the plugin. Then, the callback can decide
whether to cache the function. The callback is supposed to
return FALSE
if the statement shall not be
cached. A return value of TRUE
makes the
plugin try to add the statement into the cache. The cache entry
will be given the default TTL
(
mysqlnd_qc.ttl
). If the callback returns a
numerical value it is used as the TTL instead of the global
default.
Example 7.314 Setting a callback with
mysqlnd_qc_set_is_select
mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_statistics=1
<?php
/* callback which decides if query is cached */
function is_select($query) {
static $patterns = array(
/* true - use default from mysqlnd_qc.ttl */
"@SELECT\s+.*\s+FROM\s+test@ismU" => true,
/* 3 - use TTL = 3 seconds */
"@SELECT\s+.*\s+FROM\s+news@ismU" => 3
);
/* check if query does match pattern */
foreach ($patterns as $pattern => $ttl) {
if (preg_match($pattern, $query)) {
printf("is_select(%45s): cache\n", $query);
return $ttl;
}
}
printf("is_select(%45s): do not cache\n", $query);
return false;
}
/* install callback */
mysqlnd_qc_set_is_select("is_select");
/* Connect, create and populate test table */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
/* cache put */
$mysqli->query("SELECT id FROM test WHERE id = 1");
/* cache hit */
$mysqli->query("SELECT id FROM test WHERE id = 1");
/* cache put */
$mysqli->query("SELECT * FROM test");
$stats = mysqlnd_qc_get_core_stats();
printf("Cache put: %d\n", $stats['cache_put']);
printf("Cache hit: %d\n", $stats['cache_hit']);
?>
The above examples will output something similar to:
is_select( DROP TABLE IF EXISTS test): do not cache is_select( CREATE TABLE test(id INT)): do not cache is_select( INSERT INTO test(id) VALUES (1), (2), (3)): do not cache is_select( SELECT id FROM test WHERE id = 1): cache is_select( SELECT id FROM test WHERE id = 1): cache is_select( SELECT * FROM test): cache Cache put: 2 Cache hit: 1
The examples callback tests if a statement string matches a
pattern. If this is the case, it either returns
TRUE
to cache the statement using the
global default TTL or an alternative TTL.
To minimize application changes the callback can put into and registered in an auto prepend file.