MySQL Forums :: Performance :: Left join vs Inner join


Advanced Search

Left join vs Inner join
Posted by: Vikas Sood ()
Date: May 09, 2016 06:10PM

Hello
Been scratching my head for a couple of days now. Hoping to get some insights from more experienced folks here. I am running a query that runs under one minute if I use inner join with the sub-query. However, if I use a left join with the subquery, it runs forever. Explain plans looks almost identical in either case - I am pasting the query first, then explain plan with left join and then explain plan with inner join. Any help would be appreciated.

select aai.LoanNum
, au.AuctionKey
, aai.AssetReceivedDate
, min(aai3.AssetBidEndDateTime) as FirstAuctionDate
, a.AssetAddress
, a.AssetCity
, a.AssetState
, a.AssetZipCode
, aai.AssetListPrice
, aai.AuctionReserveAmt
, aai.FinalReserveAmt
, case when aai.AuctionStatus in ('Auction', 'Sold Presale') then 1 else 0 end as IsAuctioned
, aai2.AssetReturnedDate
, bb.AssetBidCount
, bb.MLHBidderID

from MLH_ODS.AssetAuctionInstance as aai
join MLH_ODS.Asset as a on aai.AssetId = a.AssetId
left join (select count(ab.AssetBidID) as AssetBidCount
, count(distinct ab.MLHBidderID) as MLHBidderID
, aai.AssetID
from MLH_ODS.AssetBid as ab
join MLH_ODS.AssetAuctionInstance as aai on ab.AssetAuctionInstanceID = aai.AssetAuctionInstanceID

where aai.AssetBidEndDateTime > date_add(curdate(), interval -13 month)
and aai.AssetReceivedDate between '2015-01-01' and '2016-06-30'
and ab.AutoBidInd = 0
group by aai.AssetID
order by null) as bb on bb.AssetID = a.AssetID
left join MLH_ODS.PostAuctionAssetInfo as pa on aai.AssetAuctionInstanceId = pa.AssetAuctionInstanceId
join MLH_ODS.AuctionDayStatus as ads on aai.AuctionDayStatusCode = ads.AuctionDayStatusCode
join MLH_ODS.EventVenue as ev on aai.EventVenueID = ev.EventVenueID
join MLH_ODS.Auction as au on ev.AuctionId = au.AuctionId
join MLH_ODS.Seller as s on aai.SellerID = s.SellerID
join MLH_ODS.SellerGroup as sg on s.SellerGroupID = sg.SellerGroupID
left join MLH_ODS.Outsourcer as o on aai.OutsourcerID = o.OutsourcerID
left join MLH_ODS.AssetAuctionInstance as aai2 on aai.AssetID = aai2.AssetID and aai2.AssetReturnedDate is not null #to get Return Date on all instances
left join MLH_ODS.AssetAuctionInstance as aai3 on aai.AssetID = aai3.AssetID and aai3.AuctionStatus in ('Auction', 'Sold Presale')
where aai.AssetReceivedDate between '2015-01-01' and '2016-06-30'
and sg.SellerGroupName not in ('TRUSTEE', 'FCL MARKETING')
and aai.AuctionStatus in ('Auction', 'Sold Presale')
group by aai.AssetAuctionInstanceID
********************************************************************************
1 PRIMARY au ALL PRIMARY 11070 Using temporary; Using filesort
1 PRIMARY ev ref PRIMARY,fk_EventVenue_Auction1_idx fk_EventVenue_Auction1_idx 8 MLH_ODS.au.AuctionID 2 Using index
1 PRIMARY aai ref fk_AuctionAssetInstance_Asset1_idx,fk_EventVenuAssetInstance_EventVenue1_idx,fk_AssetAuctionInstance_AuctionDayStatus1_idx,fk_AssetAuctionInstance_Seller1_idx fk_EventVenuAssetInstance_EventVenue1_idx 9 MLH_ODS.ev.EventVenueID 7 Using where
1 PRIMARY ads eq_ref PRIMARY PRIMARY 1 MLH_ODS.aai.AuctionDayStatusCode 1 Using index
1 PRIMARY a eq_ref PRIMARY PRIMARY 8 MLH_ODS.aai.AssetID 1
1 PRIMARY s eq_ref PRIMARY,fk_Seller_ProductType1_idx PRIMARY 4 MLH_ODS.aai.SellerID 1
1 PRIMARY sg eq_ref PRIMARY PRIMARY 4 MLH_ODS.s.SellerGroupID 1 Using where
1 PRIMARY o eq_ref PRIMARY PRIMARY 4 MLH_ODS.aai.OutsourcerID 1 Using index
1 PRIMARY aai2 ref fk_AuctionAssetInstance_Asset1_idx fk_AuctionAssetInstance_Asset1_idx 8 MLH_ODS.aai.AssetID 1
1 PRIMARY aai3 ref fk_AuctionAssetInstance_Asset1_idx fk_AuctionAssetInstance_Asset1_idx 8 MLH_ODS.aai.AssetID 1
1 PRIMARY <derived2> ALL 42516
1 PRIMARY pa ref fk_PostAuctionAssetInfo_AuctionAssetInstance1_idx fk_PostAuctionAssetInfo_AuctionAssetInstance1_idx 8 MLH_ODS.aai.AssetAuctionInstanceID 1 Using index
2 DERIVED ab ALL fk_AuctionAssetBidHistory_AuctionAssetInstance1_idx 7584777 Using where; Using temporary
2 DERIVED aai eq_ref PRIMARY PRIMARY 8 MLH_ODS.ab.AssetAuctionInstanceID 1 Using where
********************************************************************************

1 PRIMARY <derived2> ALL 42516 Using temporary; Using filesort
1 PRIMARY a eq_ref PRIMARY PRIMARY 8 bb.AssetID 1
1 PRIMARY aai ref fk_AuctionAssetInstance_Asset1_idx,fk_EventVenuAssetInstance_EventVenue1_idx,fk_AssetAuctionInstance_AuctionDayStatus1_idx,fk_AssetAuctionInstance_Seller1_idx fk_AuctionAssetInstance_Asset1_idx 8 bb.AssetID 1 Using where
1 PRIMARY ads eq_ref PRIMARY PRIMARY 1 MLH_ODS.aai.AuctionDayStatusCode 1 Using index
1 PRIMARY ev eq_ref PRIMARY,fk_EventVenue_Auction1_idx PRIMARY 8 MLH_ODS.aai.EventVenueID 1
1 PRIMARY au eq_ref PRIMARY PRIMARY 8 MLH_ODS.ev.AuctionID 1
1 PRIMARY s eq_ref PRIMARY,fk_Seller_ProductType1_idx PRIMARY 4 MLH_ODS.aai.SellerID 1
1 PRIMARY sg eq_ref PRIMARY PRIMARY 4 MLH_ODS.s.SellerGroupID 1 Using where
1 PRIMARY o eq_ref PRIMARY PRIMARY 4 MLH_ODS.aai.OutsourcerID 1 Using index
1 PRIMARY pa ref fk_PostAuctionAssetInfo_AuctionAssetInstance1_idx fk_PostAuctionAssetInfo_AuctionAssetInstance1_idx 8 MLH_ODS.aai.AssetAuctionInstanceID 1 Using index
1 PRIMARY aai2 ref fk_AuctionAssetInstance_Asset1_idx fk_AuctionAssetInstance_Asset1_idx 8 bb.AssetID 1
1 PRIMARY aai3 ref fk_AuctionAssetInstance_Asset1_idx fk_AuctionAssetInstance_Asset1_idx 8 bb.AssetID 1
2 DERIVED ab ALL fk_AuctionAssetBidHistory_AuctionAssetInstance1_idx 7584777 Using where; Using temporary
2 DERIVED aai eq_ref PRIMARY PRIMARY 8 MLH_ODS.ab.AssetAuctionInstanceID 1 Using where

Options: ReplyQuote


Subject Views Written By Posted
Left join vs Inner join 773 Vikas Sood 05/09/2016 06:10PM
Re: Left join vs Inner join 408 Øystein Grøvlen 05/10/2016 08:04AM
Re: Left join vs Inner join 365 Vikas Sood 05/10/2016 10:40AM
Re: Left join vs Inner join 362 Rick James 05/10/2016 07:23PM


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.