[TYPO3-dev] mysql/mysqli and prepared statements

[ries van Twisk]

On Oct 7, 2007, at 3:26 AM, Martin Kutschker wrote:

> ries van Twisk schrieb:
>>
>> One problem with this that the query plan
>> might need to change with different parameters
>> for optimal performance. However, since it's a prepared
>> query, it will not change!!! Please note that PostgreSQL
>> uses statistics to decide how to execute a query,
>
> And if you don't, you're query plan will be determined for every sinle
> statement. But, as with all optimizations, you have to check if you're
> doing the right thing. Normally you're bound query argument wouldn't
> change the whole query plan. But if it does, then of course don't  
> use a
> prepared query.

Indeed...

>
>> Also, when I look at our current SQL queries, they are really simple,
>> so it might be faster for the RDBM just to figure out the optimal
>> execution plan for each query, rather then use prepared
>> statements which can result in sub-optimal performance (see my  
>> last mail).
>
> Again, use them only in special circumstances as within a loop.  
> There is
> no need to prepare ALL statements.

exactly...

>
> Masi


I was trying to warn that a query cache is bypassed (important) for  
MySQL (see blow what version)
and that prepared queries can slowdown the process, not necessarily  
speed up.


I was digging a bit into MySQL today:
This is there statement from there website:

Before MySQL 5.1.17, prepared statements do not use the query cache.  
As of 5.1.17, prepared statements use the query cache under the  
conditions described in Section 6.5.4.1, “How the Query Cache Operates”.

This means that query cache is used for prepared statements for MySQL  
5.1.17 and higher...
I did remember I read it somewhere, but since I don't follow MySQL  
developments I am often not up to date... This is a plus. http:// 
dev.mysql.com/doc/refman/5.1/en/query-cache-how.html (some where at  
the bottom for prepared statements)


Ries