Named parameters, caching and PDO

Question

If i have a parameterized SQL statement like this:

SELECT * FROM table WHERE my_field = :field_value

Does anyone know if PDO will recognize this(see below) as the same SQL statement and use the cache instead of assuming it's a completely different SQL statement:

SELECT * FROM table WHERE my_field = :new_field_value

So, I guess the question is: if the name of a parameter changes in a parameterized select statement but nothing else changes, will I still get the performance benefit of caching? Or do I have to make sure that the parameter name stays the same?

Answer 1

It should be recognized as the same statement since the caching is done after the query parameters are replaced by values

Answer 2

PDO has no cache - MySql does. And yes, it will cache the "final" query in the query cache. Not only that, but if you use use the same prepared statements multiple times, you will gain an additional speed increase, because MySql can cache the query execution plan for that statement.

Answer 3

If you're using PDO_MySQL, it rewrites prepared statements into raw SQL on its own before the server even sees them, unless you set PDO::ATTR_EMULATE_PREPARES to false.

Answer 4

I'm not sure how PDO handles named parameters but if it uses MySQL prepared statements then you will need to use MySQL 5.1.17 or later 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. Beginning with 5.1.17, prepared statements use the query cache under certain conditions, which differ depending on the preparation method: