Description:
If a stored procedure contains queries which are literally same as the ones which exist in query cache, the query cache is missed and the statements are added separately. This addition is unexpected. It seems as if the stored procedure execution statement is also added. However if this is the case, the queries already in cache should cause a cache HIT instead of a MISS.
How to repeat:
This script can be tested in mysql test environment
CREATE TABLE t1(id int, value varchar(10));
INSERT INTO t1 VALUES(1, 'val1');
#flushing cache
SET @@GLOBAL.query_cache_size = 0;
SET @@GLOBAL.query_cache_size = 65536;
SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name Value
+Qcache_queries_in_cache 0
SELECT * FROM t1;
+id value
+1 val1
SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name Value
+Qcache_queries_in_cache 1
SELECT value FROM t1;
+value
+val1
SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name Value
+Qcache_queries_in_cache 2
# cache hit test
SELECT * FROM t1;
+id value
+1 val1
SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name Value
+Qcache_queries_in_cache 2
SELECT SQL_CACHE value FROM t1;
+value
+val1
SHOW STATUS LIKE 'Qcache_queries_in_cache';
Variable_name Value
Qcache_queries_in_cache 3
delimiter |;
CREATE PROCEDURE testProcHit()
BEGIN
SELECT * FROM t1;
SELECT SQL_CACHE value FROM t1;
END|
delimiter ;|
CALL testProcHit();
+id value
+1 val1
+value
+val1
SHOW STATUS LIKE 'Qcache_queries_in_cache';
+Variable_name Value
+Qcache_queries_in_cache 5
As the queries were same in stored procedure as in query cache results should have been 3.
Suggested fix:
The query cache should get a CACHE HIT upon execution of existing queries from a procedure.