## Setting the TTL

The default invalidation strategy of the query cache plugin is Time to Live (`TTL`). The built-in storage handlers 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. By default cache entries expire after `30` seconds

The example sets `mysqlnd_qc.ttl=3` to cache statements for three seconds by default. Every second it updates a database table record to hold the current time and executes a `SELECT` statement to fetch the record from the database. The `SELECT` statement is cached for three seconds because it is prefixed with the SQL hint enabling caching. The output verifies that the query results are taken from the cache for the duration of three seconds before they are refreshed.

Example #1 Setting the TTL with the `mysqlnd_qc.ttl` ini setting

    mysqlnd_qc.enable_qc=1
    mysqlnd_qc.ttl=3

`<?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 VARCHAR(255))");
for ($i = 0; $i < 7; $i++) {
/* update DB row */
if (!$mysqli->query("DELETE FROM test") ||
!$mysqli->query("INSERT INTO test(id) VALUES (NOW())"))
/* Of course, a real-life script should do better error handling */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
/* select latest row but cache results */
$query = "/*" . MYSQLND_QC_ENABLE_SWITCH . "*/";
$query .= "SELECT id AS _time FROM test";
if (!($res = $mysqli->query($query)) ||
!($row = $res->fetch_assoc()))
{
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
$res->free();
printf("Wall time %s - DB row time %s\n", date("H:i:s"), $row['_time']);
/* pause one second */
sleep(1);
}
?>`

The above examples will output something similar to:

    Wall time 14:55:59 - DB row time 2012-01-11 14:55:59
    Wall time 14:56:00 - DB row time 2012-01-11 14:55:59
    Wall time 14:56:01 - DB row time 2012-01-11 14:55:59
    Wall time 14:56:02 - DB row time 2012-01-11 14:56:02
    Wall time 14:56:03 - DB row time 2012-01-11 14:56:02
    Wall time 14:56:04 - DB row time 2012-01-11 14:56:02
    Wall time 14:56:05 - DB row time 2012-01-11 14:56:05

As can be seen from the example, any `TTL` based cache can serve stale data. Cache entries are not automatically invalidated, if underlying data changes. Applications using the default `TTL` invalidation strategy must be able to work correctly with stale data.

A user-defined cache storage handler can implement any invalidation strategy to work around this limitation.

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.

Example #2 Setting TTL with SQL hints

`<?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 something similar to:

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