PHP Manual

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();

?>

上の例の出力は以下となります。

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);

?>

上の例の出力は以下となります。

Default TTL     : 30 seconds
array(1) {
  ["id"]=>
  string(1) "1"
}
array(1) {
  ["id"]=>
  string(1) "1"
}
NULL
Script runtime  : 3 seconds


PHP Manual