[Atli]

So I've been looking up stuff on prepared statements and persistent connections this morning, and I came across Request #54638 in the PHP bug tracker. It states:

Quote

The PDO_MySQL driver defaults emulate_prepare to 1, which forces all prepared
queries to be emulated by the driver. This means that even though the client
library (mysqlnd or libmysql) may support prepared statements, PDO will never
really use them.

It goes on to say that this was originally done like this because MySQL 5.1.16 and earlier don't cache prepared statements and this works around that. I can sort of understand that... But it's been like five and a half years since that was fixed, and we are still defaulting to this!

Does anybody know what kind of performance and security implications this has? The main reason I use prepared statement in the first place is to avoid any possibility of SQL injection that might occur due to a bug in the escape code. That assurance is - theoretically at least - lost when this is enabled. I'd also imagine that the otherwise unnecessary escaping + injecting + parsing that occurs could have some small performance penalty. (Which I'm planing to test later.)


What I find most interesting about this is the lack of documentation. While the constant that can be used to disable this behavior (PDO::ATTR_EMULATE_PREPARES) is listed in the PDO predefined constants list, it is neither explained there nor in the PDO_MySQL page. I didn't even know, nor suspect, that this was an issue before I accidentally stumbled upon that feature request.

[e_i_pi]

To be honest, prepared statements as a performance enhancement is a bit of a joke. Unless you're running a query over and over in the same session, you're not going to get any performance improvement, as the only corner you're cutting is the creation of the execution plan, which is stored for that DB connection only. The only time you're going to benefit from this across sessions is if you use persistent connections, which are a complete pain in the rear-end, and also put the onus of DB connection management on PHP, which is a potential nightmare scenario (I brought down my live server doing this).

Something to keep in mind is that MySQL and PostgreSQL do not cache execution plans globally. MSSQL does (I believe Oracle does as well), but even MSSQL is dodgy about it. Caching execution plans of stored procedures works perfectly, but caching EPs of ad-hoc queries is flaky, with EPs sometimes being generated multiple times for the exact same query text.

If you're looking for performance enhancement in your queries, there are two server-side options you can take:

• Retrieve all the data you need up front (this can have negative consequences if your dataset is particularly large)
  
• Store the retrieved data in a PHP cache (memcache, singleton property, etc) and check to see if the data is already stored before going to the DB again (this can have negative consequences if your data changes state during the same session, though you can of course invalidate your cache if that is an issue)

This post has been edited by e_i_pi: 22 January 2013 - 04:34 PM