MySQL Forums
Forum List  »  Performance

LEFT JOIN vs INNER JOIN performance for the same amount of data returned
Posted by: Andrei Bica
Date: April 14, 2008 11:34AM

I have the following 2 queries:

1:
UPDATE location_total INNER JOIN
(SELECT SUM(expense_out.value) as total, location_total.id_location as id_location, location_total.id_period as id_period
FROM location_total INNER JOIN expense_out
USING(id_location,id_period) WHERE location_total.sync=false
GROUP BY id_location, id_period ORDER BY NULL) AS totals
USING(id_location,id_period)
SET total_expense_out=total,
sync = true;

2:
UPDATE location_total LEFT JOIN
(SELECT SUM(expense_out.value) as total, location_total.id_location as id_location, location_total.id_period as id_period
FROM location_total INNER JOIN expense_out
USING(id_location,id_period) WHERE location_total.sync=false
GROUP BY id_location, id_period ORDER BY NULL) AS totals
USING(id_location,id_period)
SET total_expense_out=total,
sync = true;


The only difference is that the second query uses a LEFT JOIN instead of INNER JOIN.

In the case that those 2 queries have the same result (the same number of rows affected because every row from the location_total have one record matched from the second temporary totals table in the case of LEFT JOIN) I have the following very different times:
- 0.798 seconds for the INNER JOIN query - 16896 affected rows
- 61 seconde for the LEFT JOIN query - 16896 affected rows

Can anyone explain the difference? The problem is related only to the JOIN operation (if I only do a select instead of the update the times have the same difference so the problem is there).

Is it because of the temporary table cannot use the indexes (all the columns involved in the joins are indexed) in the case of LEFT JOIN?

Options: ReplyQuote


Subject
Views
Written By
Posted
LEFT JOIN vs INNER JOIN performance for the same amount of data returned
27134
April 14, 2008 11:34AM


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.