Named parameters, caching and PDO
If I have a parameterized SQL statement like:
SELECT * FROM table WHERE my_field = :field_value
Does anyone know if PDO recognizes this (see below) as the same SQL statement and uses the cache instead of treating it as a completely different SQL statement:
SELECT * FROM table WHERE my_field = :new_field_value
So my guess is the question is, if the parameter name changes in a parameterized select statement, but nothing else changes, do I still get the cache advantage? Or do I need to make sure the parameter name stays the same?
If you are using PDO_MySQL, it rewrites the prepared statements into the original SQL on its own, before the server even sees them, unless you set the parameter PDO::ATTR_EMULATE_PREPARES
to false.
It should be recognized as the same operator, since caching is done after replacing query parameters with values
PDO has no cache - MySql does. And yes, it will cache the "final" request in the request cache. Not only that, but if you use the same prepared statements multiple times, you will get an additional speed boost because MySql can cache the query execution plan for that statement.
I'm not sure how PDO handles named parameters, but if it uses prepared MySQL commands then you will need to use MySQL 5.1.17 or newer if you want it to use the query cache.
MySQL query cache
Before MySQL 5.1.17, prepared statements do not use the query cache. Since 5.1.17, prepared statements use the query cache under certain conditions that differ depending on the preparation method: