Copyright 1997-2018 the PHP Documentation Group.
There are four ways to trigger caching of a query.
- Use of SQL hints on a per query basis
-
User supplied callbacks to decide on a per query basis, for example, using
mysqlnd_qc_is_select
mysqlnd_set_cache_condition
for rule based automatic per query decisionsmysqlnd_qc.cache_by_default = 1
to cache all queries blindly
Use of SQL hints and
mysqlnd_qc.cache_by_default
= 1
are explained below. Please, refer to the
function reference on
mysqlnd_qc_is_select
for a description of using a callback and,
mysqlnd_qc_set_cache_condition
on how to set rules for automatic caching.
A SQL hint is a SQL standards compliant comment. As a SQL
comment it is ignored by the database. A statement is considered
eligible for caching if it either begins with the SQL hint
enabling caching or it is a SELECT
statement.
An individual query which shall be cached must begin with the
SQL hint /*qc=on*/
. It is recommended to use
the PHP constant
MYSQLND_QC_ENABLE_SWITCH
instead of using the string value.
not eligible for caching and not cached:
INSERT INTO test(id) VALUES (1)
not eligible for caching and not cached:
SHOW ENGINES
eligible for caching but uncached:
SELECT id FROM test
eligible for caching and cached:
/*qc=on*/SELECT id FROM test
The examples SELECT
statement string is
prefixed with the
MYSQLND_QC_ENABLE_SWITCH
SQL hint to enable caching of the statement. The SQL hint must
be given at the very beginning of the statement string to enable
caching.
Example 7.308 Using the MYSQLND_QC_ENABLE_SWITCH
SQL hint
mysqlnd_qc.enable_qc=1
<?php
/* 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)");
/* Will be cached because of the SQL hint */
$start = microtime(true);
$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
printf("Total time uncached query: %.6fs\n", microtime(true) - $start);
/* Cache hit */
$start = microtime(true);
$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
printf("Total time cached query: %.6fs\n", microtime(true) - $start);
?>
The above examples will output something similar to:
array(1) { ["id"]=> string(1) "1" } Total time uncached query: 0.000740s array(1) { ["id"]=> string(1) "1" } Total time cached query: 0.000098s
If nothing else is configured, as it is the case in the
quickstart example, the plugin will use the built-in
default
storage handler. The
default
storage handler uses process memory
to hold a cache entry. Depending on the PHP deployment model, a
PHP process may serve one or more web requests. Please, consult
the web server manual for details. Details make no difference
for the examples given in the quickstart.
The query cache plugin will cache all queries regardless if the
query string begins with the SQL hint which enables caching or
not, if the PHP configuration directive
mysqlnd_qc.cache_by_default
is set to 1
. The setting
mysqlnd_qc.cache_by_default
is evaluated by the core of the query cache plugins. Neither the
built-in nor user-defined storage handler can overrule the
setting.
The SQL hint /*qc=off*/
can be used to
disable caching of individual queries if
mysqlnd_qc.cache_by_default
= 1
It is recommended to use the PHP constant
MYSQLND_QC_DISABLE_SWITCH
instead of using the string value.
Example 7.309 Using the MYSQLND_QC_DISABLE_SWITCH
SQL hint
mysqlnd_qc.enable_qc=1
mysqlnd_qc.cache_by_default=1
<?php
/* 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)");
/* Will be cached although no SQL hint is present because of mysqlnd_qc.cache_by_default = 1*/
$res = $mysqli->query("SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
$mysqli->query("DELETE FROM test WHERE id = 1");
/* Cache hit - no automatic invalidation and still valid! */
$res = $mysqli->query("SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
/* Cache miss - query must not be cached because of the SQL hint */
$res = $mysqli->query("/*" . MYSQLND_QC_DISABLE_SWITCH . "*/SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
?>
The above examples will output:
array(1) { ["id"]=> string(1) "1" } array(1) { ["id"]=> string(1) "1" } NULL
PECL/mysqlnd_qc forbids caching of statements for which at least
one column from the statements result set shows no table name in
its meta data by default. This is usually the case for columns
originating from SQL functions such as NOW()
or LAST_INSERT_ID()
. The policy aims to
prevent pitfalls if caching by default is used.
Example 7.310 Example showing which type of statements are not cached
mysqlnd_qc.enable_qc=1
mysqlnd_qc.cache_by_default=1
<?php
/* 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)");
for ($i = 0; $i < 3; $i++) {
$start = microtime(true);
/* Note: statement will not be cached because of NOW() use */
$res = $mysqli->query("SELECT id, NOW() AS _time FROM test");
$row = $res->fetch_assoc();
/* dump results */
var_dump($row);
printf("Total time: %.6fs\n", microtime(true) - $start);
/* pause one second */
sleep(1);
}
?>
The above examples will output something similar to:
array(2) { ["id"]=> string(1) "1" ["_time"]=> string(19) "2012-01-11 15:43:10" } Total time: 0.000540s array(2) { ["id"]=> string(1) "1" ["_time"]=> string(19) "2012-01-11 15:43:11" } Total time: 0.000555s array(2) { ["id"]=> string(1) "1" ["_time"]=> string(19) "2012-01-11 15:43:12" } Total time: 0.000549s
It is possible to enable caching for all statements including
those which has columns in their result set for which MySQL
reports no table, such as the statement from the example. Set
mysqlnd_qc.cache_no_table
= 1
to enable caching of such statements.
Please, note the difference in the measured times for the above
and below examples.
Example 7.311 Enabling caching for all statements using the
mysqlnd_qc.cache_no_table
ini setting
mysqlnd_qc.enable_qc=1
mysqlnd_qc.cache_by_default=1
mysqlnd_qc.cache_no_table=1
<?php
/* 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)");
for ($i = 0; $i < 3; $i++) {
$start = microtime(true);
/* Note: statement will not be cached because of NOW() use */
$res = $mysqli->query("SELECT id, NOW() AS _time FROM test");
$row = $res->fetch_assoc();
/* dump results */
var_dump($row);
printf("Total time: %.6fs\n", microtime(true) - $start);
/* pause one second */
sleep(1);
}
?>
The above examples will output something similar to:
array(2) { ["id"]=> string(1) "1" ["_time"]=> string(19) "2012-01-11 15:47:45" } Total time: 0.000546s array(2) { ["id"]=> string(1) "1" ["_time"]=> string(19) "2012-01-11 15:47:45" } Total time: 0.000187s array(2) { ["id"]=> string(1) "1" ["_time"]=> string(19) "2012-01-11 15:47:45" } Total time: 0.000167s
Although
mysqlnd_qc.cache_no_table
= 1
has been created for use with
mysqlnd_qc.cache_by_default
= 1
it is bound it. The plugin will evaluate
the
mysqlnd_qc.cache_no_table
whenever a query is to be cached, no matter whether caching
has been enabled using a SQL hint or any other measure.