Ledion B wrote:
> Mark Matthews wrote:
>
> >
> > The prepared statement code in the server is
> our
> > first "cut" at implementing prepared
> statements.
> > As such, it doesn't cache execution plans, so
> as
> > you've seen in some cases it's no quicker
> than
> > normal statements (and is sometimes slower).
>
> Well, correct me if I am wrong. For a prepared
> statement all query parsing and verification is
> done only once, (is this part of the execution
> plan ?)
It is only done once, but you have a statement that is very short, so the overhead you are saving is very, very low in your testcase.
This parsing _does_not_ include the execution plan which is the optimization stage, so no, that part is not currently cached.
[snip]
> > Also, Server-side prepared statements are not
> able
> > to take advantage of the query cache built
> into
> > the server, so if you have any reads that
> repeat,
> > those will end up being quite a bit quicker
> in
> > some cases if the table is read-only.
>
> I was just about to write about this, than when
> query caching is enabled the preparedstatement
> performs much worse than the statement. This
> brings me int a suggestion as to where is the
> cache being looked up. Does the cache simply maps
> a query string to result set before it is being
> parsed or right before the execution planning
> starts? I think the latter is more correct and
> would make cache available to prepared statements
> too.
Yes, the query cache just maps the query string to a copy of the results in the same form as they are sent "on-the-wire". Therefore it is very fast and efficient as not much overhead is needed to make it work.
To make it correctly work with prepared statements requires a more complicated caching scheme that may/or may not deliver performance gains due to having to reconstitute the query from the statement and its parameters, or alternatively employ a multi-level cache and hash the parameters per parameterized query. It is not currently on the roadmap to enable this functionality. This is also more of a server question, so you'll probably get a more detailed/better answer if you post to one of the server-performance-related forums.
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html