MySQL Forums
Forum List  »  Performance

Re: LEFT JOIN vs INNER JOIN performance for the same amount of data returned
Posted by: Clint Byrum
Date: April 15, 2008 12:51PM

I think this is just the optimizer confusing you.

Essentially what you have on the left join, is that it must run the subquery into a temptable, then look at _every_ row in location_total, since it is on the left side, and scan the temp table created by the subquery to see if any rows match id_location and id_period. With the inner join, it can run the subquery, then just pluck out the matching rows in location_total using id_location and id_period, which are probably both indexed, since the rows must exist in both to be joined. Thats also why the select is the same (you should post the explain next time, so we can understand what the optimizer sees in your tables).

In your context, I'm not sure why you'd want to left join, since you only want to update the rows with totals right?. If you do need 0's for location_total's where there are no expense_out records, you could do a second update which updates those, or you could union inside your subselect to have all the 0's come last (union select 0 as total, x,y,z, from location_total left join expense_out using (id_location,id_period) where expense_out.primarykey is null).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: LEFT JOIN vs INNER JOIN performance for the same amount of data returned
11646
April 15, 2008 12:51PM


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.