Setting the TTL
The default invalidation strategy of the query cache plugin is Time-to-Live ( TTL). The built-in storage handler will use the default TTL defined by the PHP configuration value mysqlnd_qc.ttl unless the query string contains a hint for setting a different TTL. The TTL is specified in seconds.
Any TTL based cache can serve stale data. Cache entries are not automatically invalidated, if underlying data changes.
User-defined cache storage handler can implement any invalidation strategy to work around this limitation.
<?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)");
printf("Default TTL\t: %d seconds\n", ini_get("mysqlnd_qc.ttl"));
/* Will be cached because of the SQL hint */
$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "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! */
$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
sleep(ini_get("mysqlnd_qc.ttl"));
/* Cache miss - cache entry has expired */
$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
?>
The above examples will output:
Default TTL: : 30 seconds
array(1) {
["id"]=>
string(1) "1"
}
array(1) {
["id"]=>
string(1) "1"
}
NULL
The default TTL can be overruled using the SQL hint /*qc_tt=seconds*/. The SQL hint must be appear immediately after the SQL hint which enables caching. It is recommended to use the PHP constant MYSQLND_QC_TTL_SWITCH instead of using the string value.
<?php
$start = microtime(true);
/* 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)");
printf("Default TTL\t: %d seconds\n", ini_get("mysqlnd_qc.ttl"));
/* Will be cached for 2 seconds */
$sql = sprintf("/*%s*//*%s%d*/SELECT id FROM test WHERE id = 1",
MYSQLND_QC_ENABLE_SWITCH,
MYSQLND_QC_TTL_SWITCH,
2);
$res = $mysqli->query($sql);
var_dump($res->fetch_assoc());
$res->free();
$mysqli->query("DELETE FROM test WHERE id = 1");
sleep(1);
/* Cache hit - no automatic invalidation and still valid! */
$res = $mysqli->query($sql);
var_dump($res->fetch_assoc());
$res->free();
sleep(2);
/* Cache miss - cache entry has expired */
$res = $mysqli->query($sql);
var_dump($res->fetch_assoc());
$res->free();
printf("Script runtime\t: %d seconds\n", microtime(true) - $start);
?>
The above examples will output:
Default TTL : 30 seconds
array(1) {
["id"]=>
string(1) "1"
}
array(1) {
["id"]=>
string(1) "1"
}
NULL
Script runtime : 3 seconds