Mysql – When do I need to DEALLOCATE a prepared statement in a MySQL stored procedure

MySQLprepared-statementstored-procedures

My question is simple. In what situations is it better to deallocate the prepared statement and thus removing it from memory and in what situations is it better to keep it in memory?

I have written a stored procedure that uses two prepared statements. One of the first few lines is to prepare the statement like so:

PREPARE selectStatementByJournalEntryAndLanguageQuery
FROM "INSERT INTO results(id, content, title, language_id, journalentry_id)
     SELECT *
     FROM JournalEntryTitleAndContent jetc
     WHERE jetc.language_id = ?
           AND jetc.journalentry_id = ?
     LIMIT 1";

PREPARE selectStatementByJournalEntryQuery
FROM "INSERT INTO results(id, content, title, language_id, journalentry_id)
     SELECT * FROM JournalEntryTitleAndContent jetc
     WHERE jetc.journalentry_id = ?
     LIMIT 1";

Now this stored procedure and thus these prepared statements could be used many times a day by many users. In this case it is better to not call DEALLOCATE on both prepared statements, right? Because then the prepared statements remain in memory and do not have to be recompiled when someone else uses the stored procedure.

But that raises a question about the two PREPARE statement lines. If I do not deallocate the prepared statements at the end and someone else starts the stored procedure, will the database then try to PREPARE the prepared statements again or will it ignore these two code lines when it detects that those prepared statements have already been prepared during another database stored procedure query session?

Thank you.

Best Answer

[https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html] says:

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.

A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends.

To guard against too many prepared statements being created simultaneously, set the max_prepared_stmt_count system variable. To prevent the use of prepared statements, set the value to 0.

I would note run DEALLOCATE unless I have a large number of prepared statements close to the maximum (by default 16382 in MySQL 8.0).