Hi.
On Mon, Apr 10, 2000 at 08:31:03AM +1200, quentin.bennett@stripped wrote:
> Thanks, Benjamin
>
> I managed to find a STRAIGHT_JOIN combination that did what I expected -
> i.e. restrict the action table to only those entries already known about.
>
> Unfortunately, adding an index at 13 hours a shot isn't something to be
> undertaken lightly :-). I assume, without trying it, that isamchk would take
> a similar time.
Sure. But I assume it should be not too much a hassle for you to
create a testing environment which shows the same behaviour with only
a subset of the records.
> Can you elaborate on one part of your answer:
>
> There is no index which can be used, when tt_action is read in
> first.
> MySQL has two different choices in which order it will read in the
> tables. The one it has chosen and the one you enforced with
> STRAIGHT_JOIN. The latter results in 31851594 (according to MySQL's
> guess) records to be searched in comparison to 29907570 for the one
> MySQL chooses.
>
> Why would tt_action be read first - I thought that the optimiser would look
> to read whichever table looked like resulting in the minimum work first.
Yes sure, MySQL exactly does this and I tried to explain that. It
reads tt_action first, because this variant needs 29907570 reads in
comparison the other one (the one you enforced with STRAIGHT_JOIN),
which needs 31851594 reads - at least that's what MySQL's optimizer
guesses. Of course, for us, this guess looks strange, but these are
the numbers (according to EXPLAIN), which the optimizer comes up with.
> Are the tables read in some particular order that can be influenced
> by the ordering of the WHERE clauses,
No.
> and if so how does this work when a left join is involved (the most
> restrictive part could be in the WHERE, but that comes after any ON
> clause.)
In a LEFT JOIN, the left table will be read in first, whatever the
WHERE clause is. MySQL will however, also in LEFT JOINs, use the
constraints in the WHERE clause to minimize the number of records to
read in from the right table.
As I said in my last mail, I am not sure, why MySQL comes up with
149538 records in its guesses.
Long story short: In the - for us - obvious case, where tt_action is
read second, MySQL seems to have problems to read the index (there are
some indication pro and against). Why, I don't know from the infos I
have seen. For the other case, where tt_action is read in first (as
MySQL decides to do), there is no usable index for tt_action, as
described in my last mail.
> Thanks again for you advise
You are welcome.
Bye,
Benjamin.
