I was just wondering how prepared queries work. I am using PHP and MySQL.
I know that prepared queries compile the query and then repeated uses only have to change the parameters, so it saves time. But how long does that compiled query have an effect? At which point does it have to be reevaluated? Is it just as long as the PHP script is running? Or is it as long as the connection to the database is present? In that case, would a persistent connection have any effect on it?
As far as I know, a prepared query will only "last" for as long as the variable storing it is within scope. I suppose there could be some ways to cache prepared queries for later use, but I don't know if MySQL does this.
ryeguy, they last for the length of the connection. Per the MySQL manual:
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.
If you are not using persistent connections, then this will be deallocated when your script finishes executing (or you explicitly deallocate it, or close the connection). If using persistent connections, then it will persist across multiple PHP sessions using the same persistent connection.
hobodave is right, they just last as long as the session they were created in.
There's one other thing to consider, too:
7.5.5. The MySQL Query Cache
Note
The query cache is not used for prepared statements. If you are using prepared statements, consider that these statements will not be satisfied by the query cache.