|Named parameters, caching and PDO||#1|
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?
posted date: 2008-12-18 05:07:00
|Re: Named parameters, caching and PDO||#3|
"SELECT *" always makes me shudder. :-)
posted date: 2008-12-18 05:12:00
|Re: Named parameters, caching and PDO||#4|
It should be recognized as the same statement since the caching is done after the query parameters are replaced by values
posted date: 2008-12-18 05:40:00
|Re: Named parameters, caching and PDO||#5|
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.
posted date: 2008-12-18 09:52:00
|Re: Named parameters, caching and PDO||#6|
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
posted date: 2008-12-18 09:58:00
|Re: Named parameters, caching and PDO||#7|
Doesn't that suggest that there's no performance benefit whatsoever to parameterization when using PDO with default attributes?
posted date: 2008-12-19 06:04:00
|Re: Named parameters, caching and PDO||#8|
So are you saying that it doesn't matter what name I use for parameters? As long as the syntax of the query is the same, i get the caching benefits?
posted date: 2008-12-19 06:05:00
|Re: Named parameters, caching and PDO||#9|
Oh okay. I was under the impression that parameterization is useful for performance because caching can be done without worrying about the actual values that are part of the query. But it sounds like you're saying that caching is done with the given values.
posted date: 2008-12-19 06:08:00
|Re: Named parameters, caching and PDO||#10|
No, you need to hold on to the prepared statement, to re-use it. But you can call it multiple times, binding different values each time, and get a slight benefit from that.
posted date: 2008-12-19 09:24:00
|Re: Named parameters, caching and PDO||#11|
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 CacheBefore 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:
posted date: 2008-12-20 21:53:00
|Re: Named parameters, caching and PDO||#12|
@Karim: Yes, pretty much. The default's there to make it work with MySQL 4.x. On top of no performance gains, PDO's rewriter is pretty buggy and doesn't handle edge cases well (it broke my code a few times until I learned to turn it off).
posted date: 2009-01-29 12:18:00
|select page: « 1 2 »|