Hi!
>>>>> "Tim" == Tim Bunce <[EMAIL PROTECTED]> writes:
Tim> 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...
Tim> Most high-end relational databases address this by...
Tim> a) storing the query execution plan etc in a cache keyed by the sql
Tim> statement text. That way, if another statement with the same text is
Tim> executed a ready-made execution plan is available.
Which databases are you referring to?
According to the benchmarks I have run no one of the big database
vendors does this very good by default. The problem with SQL is that
the above approach doesn't work very well when you have more than one
key in use by a query or the keys are unevenly distributed.
Even with a cached query plan, you will still loose the time for
sending the query, the parsing and initialization of the query. As the
optimizer in MySQL is only a minor part of the total time for most
queries, this will not give us any major speed increase.
Prepared statements would help a bit, at least if the developer could
give hints to the optimizer that it only need to use a given set of indexes
(This would of course fail for some queries, but generally this would
be good).
Tim> b) to make that effective they support placeholders that abstract out
Tim> literal values from the statement text, so the cached plan can be
Tim> reused regardless of the literal values boind to the placeholders
Tim> for a particular execution.
For any placeholder that is a key, we would still need to do a lot of
the work that we do today for any query.
Tim> I appreciate that doing (b) would require major changes to the protocol
Tim> etc, but it's just occured to me that there's a very simple way to
Tim> avoid that but still get the benefits of (a)...
We will add prepared statements in MySQL 4; At this point we don't intend
to cache the query plan but only avoid all parsing overhead (all
parameters will be sent in binary format, which will be MUCH faster)
Tim> Imagine if, when a statement arrived, mysqld made a char-by-char copy,
Tim> but in that copy skipped out the literal values and kept a seperate
Tim> list of those. That would be a very fast and simple piece of code.
Tim> That 'abstracted' statement could then be used as the key to the
Tim> statement cache. If it matched an entry in the cache then mysql
Tim> could skip the generation of the query execution plan!
Tim> (To simplify access rights issues you could also add the username to
Tim> the abstracted statement.)
Tim> What do you think Monty?
My estimate (which could be wrong) is that we would get a 10 % speedup
from the current setup. The major slow point here (I think) is sending the
queries between the client and server.
I will know more when we start benchmarking the embedded MySQL code,
within the next two weeks, as this setup doesn't have any TCP/IP or
socket overhead.
What we also need to do is to benchmark the different sections in the
MySQL code to be able to know how much a gain we can do by adding some
kind of cached plans. I just hope that having to store and retrieve
query plans will not cause almost as much overhead than the gain we
would get from this.
Another option would be to allow the user to specify the query plan
MySQL should use. (In other words, more options like STRAIGHT_SELECT
and use_index(...)). Theoretically this could give almost as good
performance than cached query plans.
Regards,
Monty
---------------------------------------------------------------------
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
