<!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>Pattern based caching</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.per-query-ttl.html">Setting the TTL</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.slam-defense.html">Slam defense</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.pattern-based-caching" class="section">
<h2 class="title">Pattern based caching</h2>
<p class="para">
An application has three options for telling PECL/mysqlnd_qc whether a particular
statement shall be used. The most basic approach is to cache all statements by
setting <em><a href="mysqlnd-qc.configuration.html" class="link">
mysqlnd_qc.cache_by_default = 1</a></em>. This approach is often of little
practical value. But it enables users to make a quick estimation about
the maximum performance gains from caching. An application designed to
use a cache may be able to prefix selected statements with the appropriate SQL
hints. However, altering an applications source code may not always be possible
or desired, for example, to avoid problems with software updates. Therefore,
PECL/mysqlnd_qc allows setting a callback which decides if a query is to be
cached.
</p>
<p class="para">
The callback is installed with the <span class="function"><a href="function.mysqlnd-qc-set-is-select.html" class="function">mysqlnd_qc_set_is_select()</a></span>
function. The callback is given the statement string of every statement
inspected by the plugin. Then, the callback can decide whether to cache
the function. The callback is supposed to return <strong><code>FALSE</code></strong>
if the statement shall not be cached. A return value of <strong><code>TRUE</code></strong>
makes the plugin try to add the statement into the cache. The cache entry
will be given the default TTL (<em><a href="mysqlnd-qc.configuration.html" class="link">
mysqlnd_qc.ttl</a></em>). If the callback returns
a numerical value it is used as the TTL instead of the global default.
</p>
<p class="para">
<div class="example" id="example-1852">
<p><strong>Example #1 Setting a callback with <span class="function"><a href="function.mysqlnd-qc-set-is-select.html" class="function">mysqlnd_qc_set_is_select()</a></span></strong></p>
<div class="example-contents">
<div class="inicode"><pre class="inicode">mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_statistics=1</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">/* callback which decides if query is cached */<br /></span><span style="color: #007700">function </span><span style="color: #0000BB">is_select</span><span style="color: #007700">(</span><span style="color: #0000BB">$query</span><span style="color: #007700">) {<br /> static </span><span style="color: #0000BB">$patterns </span><span style="color: #007700">= array(<br /> </span><span style="color: #FF8000">/* true - use default from mysqlnd_qc.ttl */<br /> </span><span style="color: #DD0000">"@SELECT\s+.*\s+FROM\s+test@ismU" </span><span style="color: #007700">=> </span><span style="color: #0000BB">true</span><span style="color: #007700">,<br /> </span><span style="color: #FF8000">/* 3 - use TTL = 3 seconds */<br /> </span><span style="color: #DD0000">"@SELECT\s+.*\s+FROM\s+news@ismU" </span><span style="color: #007700">=> </span><span style="color: #0000BB">3<br /> </span><span style="color: #007700">);<br /><br /> </span><span style="color: #FF8000">/* check if query does match pattern */<br /> </span><span style="color: #007700">foreach (</span><span style="color: #0000BB">$patterns </span><span style="color: #007700">as </span><span style="color: #0000BB">$pattern </span><span style="color: #007700">=> </span><span style="color: #0000BB">$ttl</span><span style="color: #007700">) {<br /> if (</span><span style="color: #0000BB">preg_match</span><span style="color: #007700">(</span><span style="color: #0000BB">$pattern</span><span style="color: #007700">, </span><span style="color: #0000BB">$query</span><span style="color: #007700">)) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"is_select(%45s): cache\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$query</span><span style="color: #007700">);<br /> return </span><span style="color: #0000BB">$ttl</span><span style="color: #007700">;<br /> }<br /> }<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"is_select(%45s): do not cache\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$query</span><span style="color: #007700">);<br /> return </span><span style="color: #0000BB">false</span><span style="color: #007700">;<br />}<br /></span><span style="color: #FF8000">/* install callback */<br /></span><span style="color: #0000BB">mysqlnd_qc_set_is_select</span><span style="color: #007700">(</span><span style="color: #DD0000">"is_select"</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), (3)"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* cache put */<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">"SELECT id FROM test WHERE id = 1"</span><span style="color: #007700">);<br /></span><span style="color: #FF8000">/* cache hit */<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">"SELECT id FROM test WHERE id = 1"</span><span style="color: #007700">);<br /></span><span style="color: #FF8000">/* cache put */<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">"SELECT * FROM test"</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$stats </span><span style="color: #007700">= </span><span style="color: #0000BB">mysqlnd_qc_get_core_stats</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"Cache put: %d\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$stats</span><span style="color: #007700">[</span><span style="color: #DD0000">'cache_put'</span><span style="color: #007700">]);<br /></span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"Cache hit: %d\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$stats</span><span style="color: #007700">[</span><span style="color: #DD0000">'cache_hit'</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>
is_select( DROP TABLE IF EXISTS test): do not cache
is_select( CREATE TABLE test(id INT)): do not cache
is_select( INSERT INTO test(id) VALUES (1), (2), (3)): do not cache
is_select( SELECT id FROM test WHERE id = 1): cache
is_select( SELECT id FROM test WHERE id = 1): cache
is_select( SELECT * FROM test): cache
Cache put: 2
Cache hit: 1
</pre></div>
</div>
</div>
</p>
<p class="para">
The examples callback tests if a statement string matches a pattern.
If this is the case, it either returns <strong><code>TRUE</code></strong> to cache
the statement using the global default TTL or an alternative TTL.
</p>
<p class="para">
To minimize application changes the callback can put into and registered
in an auto prepend file.
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.per-query-ttl.html">Setting the TTL</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.slam-defense.html">Slam defense</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>
