Hi all,
I'm benchmarking performance improvement with MySQL Query Cache turned
on but I'm facing some problem with queries inside stored procedures
when they contains variable parameters

I just created this stored procedure to identify the problem

CREATE PROCEDURE `proc_test_qcache`(IN mailbox_number VARCHAR(64)) READS
SQL DATA DETERMINISTIC
BEGIN
SELECT password FROM users WHERE mailbox = mailbox_number;
END

I see, looking at Qcache status, that calls to that stored procedure
are not cached.

Is it a known limitation ?

I think that MySQL should evaluate caching after variable substitution
but I'm not aware of MySQL internals to judge if this is the correct
behaviour.

P.S: I noticed that MySQL caches without problems queries contained in
stored procedures which does not contains variables.

Tnx in advance for

Regards

Edoardo Serra
WeBRainstorm S.r.l.

Hi,

Your questions are answered in the manual:
http://dev.mysql.com/doc/refman/5.0/...cache-how.html

It is a known limitation.

Edoardo Serra wrote:
> Hi all,
> I'm benchmarking performance improvement with MySQL Query Cache
> turned on but I'm facing some problem with queries inside stored
> procedures when they contains variable parameters
>
> I just created this stored procedure to identify the problem
>
> CREATE PROCEDURE `proc_test_qcache`(IN mailbox_number VARCHAR(64)) READS
> SQL DATA DETERMINISTIC
> BEGIN
> SELECT password FROM users WHERE mailbox = mailbox_number;
> END
>
> I see, looking at Qcache status, that calls to that stored procedure
> are not cached.
>
> Is it a known limitation ?
>
> I think that MySQL should evaluate caching after variable substitution
> but I'm not aware of MySQL internals to judge if this is the correct
> behaviour.
>
> P.S: I noticed that MySQL caches without problems queries contained in
> stored procedures which does not contains variables.
>
> Tnx in advance for
>
> Regards
>
> Edoardo Serra
> WeBRainstorm S.r.l.
>