MySQL Forums
Forum List  »  Optimizer & Parser

Re: JOIN with GROUP BY, without PRIMARY KEY
Posted by: Jerome Asselin
Date: May 23, 2006 09:39AM

KimSeong Loh wrote:
> Is B.gr your primary key?

No. The CREATE TABLE statement in my original message shows the primary key is (gr, val).

Note also that in the 1st option the optimiser will not use the same keys under the condition "where B.gr = 2". I don't understand why. So I forced the index in the 2nd option.

> The 2nd option with the force index, reads table A
> first, this will allow it to eliminate the
> temporary and sorting, but may need to read more
> rows in the join, actually all the rows in table
> A, it read from the index in this case, the whole
> index tree.

Would you then predict that the 2nd option be slower than the 1st option when table A is large? I haven't had time to try it yet.



Edited 1 time(s). Last edit at 05/23/2006 09:41AM by Jerome Asselin.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: JOIN with GROUP BY, without PRIMARY KEY
2259
May 23, 2006 09:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.