Hello Tim,
Saturday, April 07, 2001, 3:21:52 AM, you 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...
TB> Most high-end relational databases address this by...
TB> a) storing the query execution plan etc in a cache keyed by the sql
TB> statement text. That way, if another statement with the same text is
TB> executed a ready-made execution plan is available.
TB> b) to make that effective they support placeholders that abstract out
TB> literal values from the statement text, so the cached plan can be
TB> reused regardless of the literal values boind to the placeholders
TB> for a particular execution.
Well. This is not the real limitation at leas at this point. I've
checked two things
1) just "select 10" which of couse does not need much optimizations
2) select from heap table the same query as I did from myisam.
First query is about 4 times faster, and the second is at least 2
times faster then stock query and scales much petter then selecting
multiple pages by hash in (XX,XX,XX).
Both this things shows that sql parsing/commutication is not the only
point.
TB> I appreciate that doing (b) would require major changes to the protocol
TB> etc, but it's just occured to me that there's a very simple way to
TB> avoid that but still get the benefits of (a)...
TB> Imagine if, when a statement arrived, mysqld made a char-by-char copy,
TB> but in that copy skipped out the literal values and kept a seperate
TB> list of those. That would be a very fast and simple piece of code.
TB> That 'abstracted' statement could then be used as the key to the
TB> statement cache. If it matched an entry in the cache then mysql
TB> could skip the generation of the query execution plan!
TB> (To simplify access rights issues you could also add the username to
TB> the abstracted statement.)
TB> What do you think Monty?
TB> Tim.
--
Best regards,
Peter mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
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
