Basic usage
The Query Cache plugin supports caching of queries issued by the following user API calls:
-
-
PDO::query() if PDO::ATTR_EMULATE_PREPARES = 1 (default setting)
-
A query which shall be cached must begin with the SQL hint /*qc=on*/. It is recommended to use the PHP constant MYSQLND_QC_ENABLE_SWITCH instead of using the string value.
-
uncached: SELECT id FROM test
-
cached: /*qc=on*/SELECT id FROM test
Example using the most advanced PHP MySQL API, which is mysqli :
<?php
/* Enable collection of query cache statistics */
ini_set("mysqlnd_qc.collect_statistics", 1);
/* 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)");
/* Will be cached because of the SQL hint: cache put and cache_miss */
$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
/* Will NOT be cached and will NOT hit the cache: no SQL hint */
$res = $mysqli->query("SELECT id FROM test WHERE id = 2");
var_dump($res->fetch_assoc());
$res->free();
/* Display cache statistics */
$stats = mysqlnd_qc_get_core_stats();
printf("Cache hit\t: %d\n", $stats['cache_hit']);
printf("Cache miss\t: %d\n", $stats['cache_miss']);
printf("Cache put\t: %d\n", $stats['cache_put']);
?>
The above examples will output:
array(1) {
["id"]=>
string(1) "1"
}
array(1) {
["id"]=>
string(1) "2"
}
Cache hit : 0
Cache miss : 1
Cache put : 1
The default invalidation strategy of the cache is Time-to-Live ( TTL). Cache entries are valid for a certain duration. The default duration is set by the PHP configuration directive mysqlnd_qc.tll