Examples
Caching queries
Caching queries
Caching queries
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 decisions - mysqlnd_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 #1 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 #2 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 #3 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 #4 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
Note:
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.