Re: [PDO] Client side cache for prepared queries

On Tue, Aug 2, 2011 at 1:23 PM, Julien Palard <[email protected]> wrot=
e:
> Reading this, I think I should add some precisions :
>
> On Tue, Aug 2, 2011 at 11:42 AM, Goran Miskovic <[email protected]> wro=
te:
>> Beginning with MySQL 5.1.17, prepared statements use the query cache und=
er
>> certain conditions, which differ depending on the preparation method.
>> See:=C2=A0http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.h=
tml
>
> You (and the mysql documentation) are speaking about "server side
> result cache" (albeit they wrote query cache), here I'm speaking of
> "client side prepared query cache" in its strict sense. I'll try to
> make the difference clear here :
>
> =C2=A0* Caching the prepared query aimed to reuse memory structure and
> execution plan from previous prepared query.
> =C2=A0* Caching the prepared query is independent of values, we only spea=
k
> here of the prepared query not the executed query.
> =C2=A0* Caching the prepared query should be implemented client side to
> avoid one round trip.
> =C2=A0* The aim of the caching here is to reuse a prepared query, not its=
 result.
>
> Use case :
> A website, like any other, have, say, a request to fetch the content
> of a post and its comments, the query is executed once per post page,
> so a prepared query here is almost useless (Good to prevent injection,
> bad cause of an additional RTT to the sql server)
>
> If the client does prepared query cache, in this case, the RTT needed
> to tell the serveur to create the query is done only once, and each
> following pages that reuse the same SQL connection will got a cache
> hit doing the pdo::prepare, economising one RTT and the query plan
> building.
>
> Am I clear ? or not ? Wrong ? or not ?
>
> --
> Julien Palard
>
> --
> PDO Working Group Mailing List (http://pdo.php.net)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Hi.

just to be clear here.
http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html
"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:"

mysql query cache saves the results for the hash of the queries, and
it can create performance gains if you run the (exactly)same queries
over and over again.
there are problems however with this, as the algorithm behind this
isn't very smart, "If a table changes, all cached queries that use the
table become invalid and are removed from the cache."
this can make the performance gain void if your tables change frequently.
another problem with query_cache that it uses a global mutex, so it
can be affected by lock contention, which can cause serious
performance degradation.
of course you can turn off the query cache, but it seems that
unfortunately before mysql 5.5 you can't really turn off this mutex:
http://palominodb.com/blog/2011/07/06/you-cant-turn-query-cache-mysql-55

so now about your original question about prepared statements:

if you are using pdo, please notice that by default, pdo will emulate
the parameter binding, and won't use real prepared statements for
mysql:
https://bugs.php.net/bug.php?id=3D54638
of course this means that you cannot cache the prepared statements on
the server side, as your queries are normal queries,  but the query
cache will work.

if you override this, and force pdo to use real prepared statements,
then you can reuse the compiled statements via using persistent
connections.
without using persistent connections your prepared statements will be
freed as it is allocated per connection basis.

does this answer your question?

--=20
Ferenc Kov=C3=A1cs
@Tyr43l - http://tyrael.hu