Hello Michael,
Sunday, April 08, 2001, 4:21:18 AM, you wrote:
Peter>> No it's not but it has just a couple of collisions per 100.000
Peter>> records.
MW> The problem with a not unique key is that MySQL will do an extra check
MW> if it should use the key during the optimization stage. If the key
MW> would be unique, MySQL can skip this stage.
That's life :)
Peter>> Both :) I really expected about 10000 of selects per second one by
Peter>> one, and much more scaling like I got with heap table, then I got up
Peter>> to 70000 pages per second.
MW> A thing I forgot to add:
MW> - As a lot of times goes to sending/receiving data between
MW> client/server I expect that you will get more than 3600 queries /
MW> second if you are using more than one client. Have you tried this?
No I didn't, but I'm shure I'll have increase on multi cpu system,
ther on single CPU ther I connected via UNIX Domain socket almost no
cpu was idle, so I'm not shure much increase will be here.
Peter>> Well. But then I select with multiple pages selected optimizer takes
Peter>> it's work ony once, and this is well showed with heap tables, but with
Peter>> myisam this somehow is not thrue.
MW> With MyISAM, there is a lot of more system calls involved than with
MW> HEAP tables ; It could be these that slows downs things.
Well. If I understand well it's really only indeed to do an additional
rad in this case (as the table is open and the key data is in buffer)
- all of this should not take so much :)
Peter>> Other interesting thing is - if I'll tell mysql explictly to use the
Peter>> index will it reduce optimization cost ?
MW> Yes.
Peter>> It's a pity what mysql misses something like PROFILE call for the
Peter>> query so I could check how much time is spent for different phases of
Peter>> query execution.
MW> Any suggestions for the output for this?
Well Yes. It should be the table with some columns - first name of
stage, second some additional info, and the last one the time taken.
The problem is some info can be collected only on client - i.e time
to send query and time to send resive all data. Also some interesting
indo may be printed about caches (misses/hits)
Totaly it would be nice to have something like
Send query to server
pharse query
open tables
make plan
Read keys 1024 512 Hits 512 Mises
Read rows 1012 1056 Fragments
Send result to client
So the idea is to get not only timings data on different stages, but
also some additional information from different stages for example
number of key cache misses and hits.
The other odea is to have an special compile option profile - so
information will be gathered and aviable via special interface after
each query so it can be processed on application special way.
MW>> Sergei is actually working on something interesting for people that
MW>> need all the speed they can get: We are going to provide a interface
MW>> directly to the storage handler, without any optimizations.
MW>> This will of course not be SQL, but we expect this to be VERY fast as
MW>> long as you are searching on a specific key...
Peter>> Yes it's nice. The other possible speedup is prepeared statements, or
Peter>> execution plan cache (classical solutions) :)
MW> We will add prepared statements in MySQL 4; The question is will these
MW> really help your basic setup?
Only profiling would show this.
Peter>> OK. This is not really pain - just comments :)
Peter>> My current pains are problems with repair tables and.... keycache :)
MW> No comments (yet).
:)
--
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
