MySQL Forums
Forum List  »  Optimizer & Parser

left outer join performance
Posted by: Michael Shavlovsky
Date: November 28, 2011 09:58PM

Hi there!
I have interesting observation on left outer join performance, cant understand it. In one case this operation works 6 hours in other case 4 seconds on same number of rows (200k)! Why?

Need to joint two table DIH and Members on memberID. Code provided below works 6 hours to join 200k rows.

create index idx1 on DIH (MemberID_t);
create index idx2 on Members (MemberID_M);
create table temp1
select a.*, b.*
from DIH a left outer join Members b
on a.MemberID_t = b.MemberID_M;

But code to merge other tables temp1 and drugcount works 4 seconds with the same number of rows.

create index idx3 on temp1(Memberid_t);
create index idx4 on temp1(YEAR_t);
create index idx5 on DRUGCOUNT(Memberid_DC);
create index idx6 on DRUGCOUNT(YEAR_dc);

create table temp2
select a.*, b.*
from temp1 a left outer join DRUGCOUNT b
on a.Memberid_t = b.Memberid_DC
and a.YEAR_t = b.YEAR_dc;

What is the reason of such difference? and how to make first "join" work faster?

Options: ReplyQuote

Written By
left outer join performance
November 28, 2011 09:58PM
November 28, 2011 11:10PM
November 29, 2011 09:27PM

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.