Hi.
On Thu, Feb 15, 2001 at 03:53:39PM -0500, [EMAIL PROTECTED] wrote:
> 15/02/01 15:14, Quentin Bennett, [EMAIL PROTECTED]:
[...]
> > Adding the author to the query means that the query now has to go to the
> > data file to get the information, and since there is no restriction on isbn,
> > the entire table is scanned for records to provide the result, which is then
> > sorted. If you had an index of (isbn, author), then the index file would
> > still be used.
>
> That's where I don't get it, I don't want to sort by author, only by isbn.
> Why does mysql needs to resort to a filesort while it has all the ISBN
> neatly ordered in an index?
You have to look at it the other way around:
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.
> > Can someone else tell me and Francois, when the index is used like this, is
> > the result pre-sorted, so the order by is trivial, or is a sort still
> > performed.
If the index is used, the data is read in-order and therefore the sort
is skipped, AFAIK.
> Yes, how can we get mySQL to use an index for doing an order by? Must I
> create an index for each and every possible fields combination?
There are several reasonable approaches and which to use depends on
your environment and usage.
One is almost the one you propose, but not indexes for all
combinations, but only for the common ones (you are restricted in the
number of indexes).
Since the time difference is quite large, your data file seems to be
quite big and you may want to consider to split it into two tables, if
the scanned fields (like author,isbn) are relatively small compared to
the rest of the record. One table would contain all small fields that
are needed in the full table scans and the other table the rest.
On the other hand, you may question yourself whether these full tables
scans are really needed that much (it sounds rather unusual).
Bye,
Benjamin.
---------------------------------------------------------------------
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
