Bug #35388 Stored procedure execution causes improper behavior of query cache.
Submitted: 18 Mar 2008 10:45 Modified: 7 Jul 2017 9:43
Reporter: Rizwan Maredia Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.1.22, 5.0, 5.1, 6.0 BK OS:Any
Assigned to: CPU Architecture:Any
Tags: query cache, query_cache_size

[18 Mar 2008 10:45] Rizwan Maredia
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.
[18 Mar 2008 20:09] Sveta Smirnova
Thank you for the report.

Verified as described.
[7 Jul 2017 9:43] Erlend Dahl
MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/