Left join vs Inner join
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