Mysql - Stored procedure not using the query cache

Question

I have just converted a SQL select statement into a stored procedure

The SQL Statement use select statement takes 0.4784s to run the first time and 0.0003s after that

The Stored procedure takes 0.4784s to run every time.

I assume the query cache is not been used

How can I rectify this?

A simplified version of the code

SELECT * FROM  Venues WHERE  VenueName  = :TheVenue

=======

CREATE PROCEDURE  GetVenues
(
  TheVenue  VarChar(22)
)
BEGIN
    SELECT * FROM  Venues WHERE  VenueName  =   TheVenue 
END;

Answer 1

You could try a dynamic SQL stored procedure, like:

CREATE PROCEDURE GetVenues (TheVenue varchar(22)) 
BEGIN 
SET @s = 'SELECT * FROM Venues WHERE VenueName = ?'; 
SET @v = TheVenue;
PREPARE stmt1 FROM @s; 
EXECUTE stmt1 USING @v; 
DEALLOCATE PREPARE stmt1; 
END;

No MySQL server by hand to test the syntax, so you might have to tweak it.

Answer 2

Welcome to MySQL... it is really difficult to get anything within a stored procedure to take advantage of the query cache. The dev article A Practical Look at the MySQL Query Cache discusses this in some detail. The limitations are also mentioned in the reference documentation here and on the MySQL Performance Blog.

Basically, don't depend on caching of queries executed inside of stored procedures. It is near impossible to get it to work though the first reference does claim that it is possible. This usually isn't a problem if you are using stored procedures to encapsulate complicated logic. Most of the problems that I have seen were caused by using stored procedures for very simple queries where a VIEW would have sufficed.