Named parameters, caching and PDO

Tags: pdo mysql sql php
By : Karim
Source: Stackoverflow.com
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?

By : Karim


Answers

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:

By : ejunker


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.

By : flussence


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.

By : troelskn


This video can help you solving your question :)
By: admin