Should prepared statements be deallocated when used inside stored procedures?

Question

Hi!

When using prepared statements inside stored procedures, should they be deallocated at the end of the procedure or not, or does it not matter, and why?

Some code to explain:

CREATE PROCEDURE getCompanyByName (IN name VARCHAR(100))
NOT DETERMINISTIC
BEGIN
  PREPARE gcbnStatement FROM 'SELECT * FROM Companies WHERE name=? LIMIT 1';
  SET @companyName = name;
  EXECUTE gcbnStatement USING @companyName;
  DEALLOCATE PREPARE gcbnStatement;
END $$

So - should the DEALLOCATE statement be there or not? Cheers!

/Victor

Answer 1

According to the MySQL docs:

A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically.

So, no, I wouldn't bother doing it explicitly, unless you have very long-running sessions.

Answer 2

If you use connection pooling, it is definitely a good idea to deallocate them.