Benjamin Pflugmann wrote :
>
> Hi.
> [....]
>
> If MySQL uses the index, it has one of two possibilities.
>
> First:
> 1. Read the whole isbn index, and for each row
> 2. seek the record in the data file and get author from it.
> This is quite slow because of the random file seeks needed.
>
> Second:
> 1. Read the whole isbn index (since you have no restricting WHERE clause)
> 2. Read isbn,author from the whole data file in unsorted order (you
> need isbn, too, else MySQL wouldn't know where the authors belong too)
> 3. Sort isbn,autor (using sorted isbn, so using selection sort?)
>
> This is still slower than MySQL's approch:
>
> Third:
> 1. Read isbn,author from the whole data file in unsorted order
> 2. Sort isbn,autor
>
> as "Second" additionally has to read the index file from disk with no
> additional benefit.
>
> I don't know how to express that elegant, but the problem is that by
> including author to the queried fields, you have an unsorted column
> which requires a full sort.
>
> "Second" is never the best way. MySQL uses "First" up to some
> percentage of the amount of records (30% I believe), then uses
> "Third", because reading the data in file order (unsorted) and sorting
> is usually faster than random seeks (due to index usage) over that
> percentage of records.
>
Hi all,
Sorry to jump in the middle of this thread but your comment make me
think
of a case where random seeks may still be better even if more than
30% of records are involved:
When you set up your database server so that it has enough RAM to keep
the
whole database in main memory (buffer cache), random reads don't need
to wait for physical disk seeks and can be very fast.
This is actually the case of our database server (2Gb RAM, 500 Mb
database);
vmstat shows that MySQL can run for hours without any disk read even
under high load (more than 300 queries/s).
So my question is:
Is it a way to tune this threshold ?
(In the case described above, it may still be faster to use random seeks
up to 60 a 70% instead of 30%).
>
> Bye,
Thanks
--
Joseph Bueno
NetClub/Trader.com
---------------------------------------------------------------------
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
