On Sat, Apr 07, 2001 at 02:14:42AM +0300, Michael Widenius wrote:
>
> The reason singly queries are slower are of course that the
> initialization phase (reading the data from a socket, parsing,
> locking, checking which keys to use) is about half of the query time.
>
> Peter> Heikki made tests which also shows some strange things - for
> Peter> example why INSERT is FASTER then SELECT.
>
> I haven't seen the test but I can imagine this is true in some
> context. The reason for this is that a SELECT has to go through many
> optimization stages to find out what indexes to use and what queries
> to do. This is one basic fault with SQL; The optimizer has to do a
> lot of work...
Most high-end relational databases address this by...
a) storing the query execution plan etc in a cache keyed by the sql
statement text. That way, if another statement with the same text is
executed a ready-made execution plan is available.
b) to make that effective they support placeholders that abstract out
literal values from the statement text, so the cached plan can be
reused regardless of the literal values boind to the placeholders
for a particular execution.
I appreciate that doing (b) would require major changes to the protocol
etc, but it's just occured to me that there's a very simple way to
avoid that but still get the benefits of (a)...
Imagine if, when a statement arrived, mysqld made a char-by-char copy,
but in that copy skipped out the literal values and kept a seperate
list of those. That would be a very fast and simple piece of code.
That 'abstracted' statement could then be used as the key to the
statement cache. If it matched an entry in the cache then mysql
could skip the generation of the query execution plan!
(To simplify access rights issues you could also add the username to
the abstracted statement.)
What do you think Monty?
Tim.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
