Leaving aside the benefit for security, does mysqli prepared statements have any performance benefit?
As I understand it, the prepare stores the query in parsed form at the database server, and when doing execute, then only the data for any values are passed from script to database.
Direct benefit would be that the database only need to parse SQL once, and that less bytes (only the values) are sent on each query.
However, is this not only a benefit if the query is executed repeatedly inside the script, or does MySQL store the prepared statement between requests? If so, for how long?
Statements are linked to a connection, so as soon as the connection is terminated, the stored statements are freed (at least, that's my understanding. Someone can correct me if I'm wrong). As a result, prepared statements in any language tend to suffer a very slight performance decrease with single statements and a modest performance increase with very many repeated queries per connection.
Security is the real reason, though. As I like to put it: the difference in total time spent between a standard single query and a prepared single query will never be sufficient, over all the queries run on your server, to come within two orders of magnitude of the time it will take you to clean up one SQL Injection attack.
Prepared statements are slower unless you're using them as they're intended and you're running the same query multiple times with different parameters.
They only live in the connection. In fact, they only live as long as you reuse the same resource. Eg. If you prepare the same query twice, you will not reuse the first one. You need to hold on to that prepared statement.
There is some performance gain, if you're calling the same query multiple times. I'm not sure how many, but if you're calling hundreds or thousands of queries, it makes a difference. If you're looking to boost performance on write operations, a good trick is to use a transaction. Since indexes are only updated once the transaction is committed, rather than between each query, you can often gain a lot by this simple trick.
Thanks guys, that confirms my suspicion.
Regarding lifetime of how long MySQL store the prepared statement, will it survive across requests if using persistent connection, and is there then a way to check if already prepared?
I'm thinking of simple queries that are executed each time a script is called. I don't think it may apply in this case, but I'll keep the transaction trick in mind
It won't persist, because it creates a stmt object related to the prepared statement. There's no way (at least afaik) to retrieve this on subsequent requests.
Whether or not its a noticeable difference will be dependent on the environment.
You're right, of course, when it comes to speed. From what I've seen, not having your indexes right, not using efficient queries, using many queries to do something you could do with one: those are things that will really slow you down.
Every time I see this question, I have the sudden urge to shout: yes, they might impose a (very marginal) performance decrease, but you should use them none the less: security is more important than speed in my book, and this way, security is implied without you having to do anything about it.
Well the OP specifically wanted to discuss performance ignoring security benefits and prepared statements aren't the only way to secure your queries.
As for "using many queries to do something you could do with one" that's not always slower. This was something I firmly believed too until Czaries questioned my assumption here: http://www.sitepoint.com/forums/showpost.php?p=4498712&postcount=10
If you benchmark it, he's right.
I know he wants to wants discuss the performance, but from where I'm standing, performance also includes the hoops you'll have to jump when you have to escape manually. I just thought it should be said at least once
I know that, I wasn't trying to say one query is always more efficient, but there certainly are cases where that is true. You'll have to try and see for each case individually though.