Description:
http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html
> A query also is not cached under these conditions:
> It refers to ... stored functions.
We have a select, which can not be expressed in simple SQL, but can easily be expressed as stored function.
That function is "DETERMINISTIC", and "READS SQL DATA".
Could be cached.
It does not get cached, and we're forced to use some different caching layer, while internal MySQL caching is better.
How to repeat:
create table test1(a int);
CREATE FUNCTION test() RETURNS int(11)
DETERMINISTIC
READS SQL DATA
begin
return 0;
end
select sql_cache * from table1 where a = test();
show global status like 'Qcache_hits'; --remember value
select sql_cache * from table1 where a = test();
show global status like 'Qcache_hits'; --will show same value
Suggested fix:
--------------------option1
We feel that existing "READS SQL DATA" implies that existing approach is to free compiler of stored function from the burden of figuring out "what exactly this function is accessing?".
If we go along existing approach, we can add a modifier to CREATE FUNCTION statement:
CACHABLE(<list of source tables>)
Example:
CREATE FUNCTION test() RETURNS int(11)
DETERMINISTIC
READS SQL DATA
CACHABLE(Table1, Table2)
begin
...
end
--------------------option2
Collect list of table names, that are addressed from stored function/procedure, including all nested calls.
Add "CACHABLE" modifier without parameters to "CREATE FUNCITON" statement.
CREATE FUNCTION test() RETURNS int(11)
DETERMINISTIC
READS SQL DATA
begin
...accesses Table1 and Table2, detect that during prepared statement processing...
end
