<!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>Returns a normalized query trace log for each query inspected by the query cache</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.mysqlnd-qc-get-core-stats.html">mysqlnd_qc_get_core_stats</a></div>
<div class="next" style="text-align: right; float: right;"><a href="function.mysqlnd-qc-get-query-trace-log.html">mysqlnd_qc_get_query_trace_log</a></div>
<div class="up"><a href="ref.mysqlnd-qc.html">mysqlnd_qc Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="function.mysqlnd-qc-get-normalized-query-trace-log" class="refentry">
<div class="refnamediv">
<h1 class="refname">mysqlnd_qc_get_normalized_query_trace_log</h1>
<p class="verinfo">(PECL mysqlnd_qc >= 1.0.0)</p><p class="refpurpose"><span class="refname">mysqlnd_qc_get_normalized_query_trace_log</span> — <span class="dc-title">Returns a normalized query trace log for each query inspected by the query cache</span></p>
</div>
<div class="refsect1 description" id="refsect1-function.mysqlnd-qc-get-normalized-query-trace-log-description">
<h3 class="title">Description</h3>
<div class="methodsynopsis dc-description">
<span class="type">array</span>
<span class="methodname"><strong>mysqlnd_qc_get_normalized_query_trace_log</strong></span>
( <span class="methodparam">void</span>
)</div>
<p class="para rdfs-comment">
Returns a normalized query trace log for each query inspected by the query cache.
The collection of the trace log is disabled by default. To collect
the trace log you have to set the PHP configuration directive
<em>mysqlnd_qc.collect_normalized_query_trace</em> to
<em>1</em>
</p>
<p class="para">
Entries in the trace log are grouped by the normalized query statement.
The normalized query statement is the query statement with all statement
parameter values being replaced with a question mark. For example, the two
statements <em>SELECT id FROM test WHERE id = 1</em> and
<em>SELECT id FROM test WHERE id = 2</em> are normalized as
<em>SELECT id FROM test WHERE id = ?</em>. Whenever a statement
is inspected by the query cache which matches the normalized statement pattern,
its statistics are grouped by the normalized statement string.
</p>
</div>
<div class="refsect1 parameters" id="refsect1-function.mysqlnd-qc-get-normalized-query-trace-log-parameters">
<h3 class="title">Parameters</h3>
<p class="para">This function has no parameters.</p>
</div>
<div class="refsect1 returnvalues" id="refsect1-function.mysqlnd-qc-get-normalized-query-trace-log-returnvalues">
<h3 class="title">Return Values</h3>
<p class="para">
An array of query log. Every list entry contains
the normalized query stringand further detail information.
</p>
<table class="doctable informaltable">
<col width="1*" />
<col width="9*" />
<thead>
<tr>
<th>Key</th>
<th>Description</th>
</tr>
</thead>
<tbody class="tbody">
<tr>
<td>
<em>query</em>
</td>
<td>
Normalized statement string.
</td>
</tr>
<tr>
<td>
<em>occurences</em>
</td>
<td>
How many statements have matched the normalized statement
string in addition to the one which has created the log entry.
The value is zero if a statement has been normalized, its normalized
representation has been added to the log but no further queries inspected
by PECL/mysqlnd_qc have the same normalized statement string.
</td>
</tr>
<tr>
<td>
<em>eligible_for_caching</em>
</td>
<td>
Whether the statement could be cached. An statement eligible for
caching has not necessarily been cached. It not possible to tell
for sure if or how many cached statement have contributed to the
aggregated normalized statement log entry. However, comparing
the minimum and average run time one can make an educated guess.
</td>
</tr>
<tr>
<td>
<em>avg_run_time</em>
</td>
<td>
The average run time of all queries contributing to the
query log entry. The run time is the time between sending the
query statement to MySQL and receiving an answer from MySQL.
</td>
</tr>
<tr>
<td>
<em>avg_store_time</em>
</td>
<td>
The average store time of all queries contributing to the
query log entry. The store time is the time needed to fetch
a statements result set from the server to the client and,
storing it on the client.
</td>
</tr>
<tr>
<td>
<em>min_run_time</em>
</td>
<td>
The minimum run time of all queries contributing to the
query log entry.
</td>
</tr>
<tr>
<td>
<em>min_store_time</em>
</td>
<td>
The minimum store time of all queries contributing to the
query log entry.
</td>
</tr>
<tr>
<td>
<em>max_run_time</em>
</td>
<td>
The maximum run time of all queries contributing to the
query log entry.
</td>
</tr>
<tr>
<td>
<em>max_store_time</em>
</td>
<td>
The maximum store time of all queries contributing to the
query log entry.
</td>
</tr>
</tbody>
</table>
</div>
<div class="refsect1 examples" id="refsect1-function.mysqlnd-qc-get-normalized-query-trace-log-examples">
<h3 class="title">Examples</h3>
<div class="example" id="example-1865">
<p><strong>Example #1 <span class="function"><strong>mysqlnd_qc_get_normalized_query_trace_log()</strong></span> example</strong></p>
<div class="example-contents">
<div class="inicode"><pre class="inicode">mysqlnd_qc.collect_normalized_query_trace=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">/* 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: #FF8000">/* not cached */<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: #DD0000">"SELECT id FROM test WHERE id = 1"</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: #FF8000">/* cache put */<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: #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">. </span><span style="color: #DD0000">"SELECT id FROM test WHERE id = 2"</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: #FF8000">/* cache hit */<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: #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">. </span><span style="color: #DD0000">"SELECT id FROM test WHERE id = 2"</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">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">mysqlnd_qc_get_normalized_query_trace_log</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:</p></div>
<div class="example-contents screen">
<div class="cdata"><pre>
array(1) {
["id"]=>
string(1) "1"
}
array(1) {
["id"]=>
string(1) "2"
}
array(1) {
["id"]=>
string(1) "2"
}
array(4) {
[0]=>
array(9) {
["query"]=>
string(25) "DROP TABLE IF EXISTS test"
["occurences"]=>
int(0)
["eligible_for_caching"]=>
bool(false)
["avg_run_time"]=>
int(0)
["min_run_time"]=>
int(0)
["max_run_time"]=>
int(0)
["avg_store_time"]=>
int(0)
["min_store_time"]=>
int(0)
["max_store_time"]=>
int(0)
}
[1]=>
array(9) {
["query"]=>
string(27) "CREATE TABLE test (id INT )"
["occurences"]=>
int(0)
["eligible_for_caching"]=>
bool(false)
["avg_run_time"]=>
int(0)
["min_run_time"]=>
int(0)
["max_run_time"]=>
int(0)
["avg_store_time"]=>
int(0)
["min_store_time"]=>
int(0)
["max_store_time"]=>
int(0)
}
[2]=>
array(9) {
["query"]=>
string(40) "INSERT INTO test (id ) VALUES (? ), (? )"
["occurences"]=>
int(0)
["eligible_for_caching"]=>
bool(false)
["avg_run_time"]=>
int(0)
["min_run_time"]=>
int(0)
["max_run_time"]=>
int(0)
["avg_store_time"]=>
int(0)
["min_store_time"]=>
int(0)
["max_store_time"]=>
int(0)
}
[3]=>
array(9) {
["query"]=>
string(31) "SELECT id FROM test WHERE id =?"
["occurences"]=>
int(2)
["eligible_for_caching"]=>
bool(true)
["avg_run_time"]=>
int(159)
["min_run_time"]=>
int(12)
["max_run_time"]=>
int(307)
["avg_store_time"]=>
int(10)
["min_store_time"]=>
int(8)
["max_store_time"]=>
int(13)
}
}
</pre></div>
</div>
</div>
</div>
<div class="refsect1 seealso" id="refsect1-function.mysqlnd-qc-get-normalized-query-trace-log-seealso">
<h3 class="title">See Also</h3>
<p class="para">
<ul class="simplelist">
<li class="member">
<a href="mysqlnd-qc.configuration.html" class="link">Runtime configuration</a>
</li>
<li class="member">
<a href="mysqlnd-qc.configuration.html#ini.mysqlnd-qc.collect-query-trace" class="link">mysqlnd_qc.collect_normalized_query_trace</a>
</li>
<li class="member">
<a href="mysqlnd-qc.configuration.html#ini.mysqlnd-qc.time-statistics" class="link">mysqlnd_qc.time_statistics</a>
</li>
<li class="member">
<span class="function"><a href="function.mysqlnd-qc-get-query-trace-log.html" class="function" rel="rdfs-seeAlso">mysqlnd_qc_get_query_trace_log()</a> - Returns a backtrace for each query inspected by the query cache</span>
</li>
</ul>
</p>
</div>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.mysqlnd-qc-get-core-stats.html">mysqlnd_qc_get_core_stats</a></div>
<div class="next" style="text-align: right; float: right;"><a href="function.mysqlnd-qc-get-query-trace-log.html">mysqlnd_qc_get_query_trace_log</a></div>
<div class="up"><a href="ref.mysqlnd-qc.html">mysqlnd_qc Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
