Freeing up PDO prepared statements (DEALLOCATE PREPARE)

Question

Should PDO prepared statements be freed up after use? And if so, how? Specifically I'm asking about MySQL - how can you, and should you, call DEALLOCATE PREPARE though PDO. (Edit: To clarify, this question is not referring to emulated prepares, but real prepares. )

Also - will this free the results set (when large)?

Explanation:

I have seen code along the lines of

$stmnt = $db->prepare($sql);
$stmnt->execute($aParams);
$stmnt = null;

which led me to wondering what this does, when, and if f unset($stmnt); would be different?

The manual indicates that

When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. [...] By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle.

which tends to suggest you should unallocate the statement, and MySQL has the capability. So,

1. Can you call DEALLOCATE PREPARE, and how
2. Should you do it?
3. And can anyone confirm that setting statement to null (or unsetting the statement) will do the same as "free_result" for mysql_ and mysqli_?
4. Does it happen immediately, or does it wait for garbage collector to kick in?

---

For completeness, another SO question referring to "free_result" and "close" functions for mysqli_() suggests that freeing the statement actually adds time (unless you have large memory usage and need the space). But "free_result" is different from freeing the SQL server from having the prepared statment cached.

Answer 1

Yes. When you are done with the prepare statement you can set it to NULL or use unset().

For a script with multiple queries and large databases, this makes a difference. You can test with:

$before = memory_get_usage();
$stmt = NULL;
die(memory_get_usage() - $before);

For me, this saved 20MB of memory, which was crashing the script later.