LEFT JOIN vs INNER JOIN performance for the same amount of data returned

[Andrei Bica]

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?