<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Setting the TTL</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.quickstart.caching.html">Caching queries</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.pattern-based-caching.html">Pattern based caching</a></div>
<div class="up"><a href="mysqlnd-qc.quickstart.html">Quickstart and Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-qc.per-query-ttl" class="section">
<h2 class="title">Setting the TTL</h2>
<p class="para">
The default invalidation strategy of the query cache plugin is Time to Live
(<em>TTL</em>). The built-in storage handlers will use the default
<em>TTL</em> defined by the PHP configuration value
<em><a href="mysqlnd-qc.configuration.html" class="link">mysqlnd_qc.ttl</a></em>
unless the query string contains a hint for setting a different
<em>TTL</em>. The <em>TTL</em> is specified in seconds.
By default cache entries expire after <em>30</em> seconds
</p>
<p class="para">
The example sets <em>mysqlnd_qc.ttl=3</em> to cache
statements for three seconds by default. Every second it updates
a database table record to hold the current time and executes
a <em>SELECT</em> statement to fetch the record from the
database. The <em>SELECT</em> 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.
</p>
<p class="para">
<div class="example" id="example-1850">
<p><strong>Example #1 Setting the TTL with the <em>mysqlnd_qc.ttl</em> ini setting</strong></p>
<div class="example-contents">
<div class="inicode"><pre class="inicode">mysqlnd_qc.enable_qc=1
mysqlnd_qc.ttl=3</pre>
</div>
</div>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /></span><span style="color: #FF8000">/* Connect, create and populate test table */<br /></span><span style="color: #0000BB">$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"host"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"schema"</span><span style="color: #007700">, </span><span style="color: #DD0000">"port"</span><span style="color: #007700">, </span><span style="color: #DD0000">"socket"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP TABLE IF EXISTS test"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE TABLE test(id VARCHAR(255))"</span><span style="color: #007700">);<br /><br />for (</span><span style="color: #0000BB">$i </span><span style="color: #007700">= </span><span style="color: #0000BB">0</span><span style="color: #007700">; </span><span style="color: #0000BB">$i </span><span style="color: #007700">< </span><span style="color: #0000BB">7</span><span style="color: #007700">; </span><span style="color: #0000BB">$i</span><span style="color: #007700">++) {<br /><br /> </span><span style="color: #FF8000">/* update DB row */<br /> </span><span style="color: #007700">if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DELETE FROM test"</span><span style="color: #007700">) ||<br /> !</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO test(id) VALUES (NOW())"</span><span style="color: #007700">))<br /> </span><span style="color: #FF8000">/* Of course, a real-life script should do better error handling */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">));<br /><br /> </span><span style="color: #FF8000">/* select latest row but cache results */<br /> </span><span style="color: #0000BB">$query </span><span style="color: #007700">= </span><span style="color: #DD0000">"/*" </span><span style="color: #007700">. </span><span style="color: #0000BB">MYSQLND_QC_ENABLE_SWITCH </span><span style="color: #007700">. </span><span style="color: #DD0000">"*/"</span><span style="color: #007700">;<br /> </span><span style="color: #0000BB">$query </span><span style="color: #007700">.= </span><span style="color: #DD0000">"SELECT id AS _time FROM test"</span><span style="color: #007700">;<br /> if (!(</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #0000BB">$query</span><span style="color: #007700">)) ||<br /> !(</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">()))<br /> {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br /> }<br /> </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">free</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"Wall time %s - DB row time %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">date</span><span style="color: #007700">(</span><span style="color: #DD0000">"H:i:s"</span><span style="color: #007700">), </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'_time'</span><span style="color: #007700">]);<br /><br /> </span><span style="color: #FF8000">/* pause one second */<br /> </span><span style="color: #0000BB">sleep</span><span style="color: #007700">(</span><span style="color: #0000BB">1</span><span style="color: #007700">);<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
<div class="example-contents"><p>The above examples will output
something similar to:</p></div>
<div class="example-contents screen">
<div class="cdata"><pre>
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
</pre></div>
</div>
</div>
</p>
<p class="para">
As can be seen from the example, any <em>TTL</em> based cache
can serve stale data. Cache entries are not automatically invalidated,
if underlying data changes. Applications using the default
<em>TTL</em> invalidation strategy must be able to work correctly
with stale data.
</p>
<p class="para">
A user-defined cache storage handler can implement any invalidation strategy
to work around this limitation.
</p>
<p class="para">
The default <em>TTL</em> can be overruled using the SQL hint
<em>/*qc_tt=seconds*/</em>. The SQL hint must be appear immediately
after the SQL hint which enables caching. It is recommended to use the PHP constant
<em><a href="mysqlnd-qc.constants.html" class="link">MYSQLND_QC_TTL_SWITCH</a></em>
instead of using the string value.
</p>
<p class="para">
<div class="example" id="example-1851">
<p><strong>Example #2 Setting TTL with SQL hints</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$start </span><span style="color: #007700">= </span><span style="color: #0000BB">microtime</span><span style="color: #007700">(</span><span style="color: #0000BB">true</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* Connect, create and populate test table */<br /></span><span style="color: #0000BB">$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"host"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"schema"</span><span style="color: #007700">, </span><span style="color: #DD0000">"port"</span><span style="color: #007700">, </span><span style="color: #DD0000">"socket"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP TABLE IF EXISTS test"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE TABLE test(id INT)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO test(id) VALUES (1), (2)"</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"Default TTL\t: %d seconds\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">ini_get</span><span style="color: #007700">(</span><span style="color: #DD0000">"mysqlnd_qc.ttl"</span><span style="color: #007700">));<br /><br /></span><span style="color: #FF8000">/* Will be cached for 2 seconds */<br /></span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"/*%s*//*%s%d*/SELECT id FROM test WHERE id = 1"</span><span style="color: #007700">, </span><span style="color: #0000BB">MYSQLND_QC_ENABLE_SWITCH</span><span style="color: #007700">, </span><span style="color: #0000BB">MYSQLND_QC_TTL_SWITCH</span><span style="color: #007700">, </span><span style="color: #0000BB">2</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">free</span><span style="color: #007700">();<br /><br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DELETE FROM test WHERE id = 1"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">sleep</span><span style="color: #007700">(</span><span style="color: #0000BB">1</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* Cache hit - no automatic invalidation and still valid! */<br /></span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">free</span><span style="color: #007700">();<br /><br /></span><span style="color: #0000BB">sleep</span><span style="color: #007700">(</span><span style="color: #0000BB">2</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* Cache miss - cache entry has expired */<br /></span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">free</span><span style="color: #007700">();<br /><br /></span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"Script runtime\t: %d seconds\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">microtime</span><span style="color: #007700">(</span><span style="color: #0000BB">true</span><span style="color: #007700">) - </span><span style="color: #0000BB">$start</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
<div class="example-contents"><p>The above examples will output
something similar to:</p></div>
<div class="example-contents screen">
<div class="cdata"><pre>
Default TTL : 30 seconds
array(1) {
["id"]=>
string(1) "1"
}
array(1) {
["id"]=>
string(1) "1"
}
NULL
Script runtime : 3 seconds
</pre></div>
</div>
</div>
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.quickstart.caching.html">Caching queries</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.pattern-based-caching.html">Pattern based caching</a></div>
<div class="up"><a href="mysqlnd-qc.quickstart.html">Quickstart and Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
