Re: LEFT JOIN vs INNER JOIN performance for the same amount of data returned
Thanks for the answer Clint.
Yes, thats right, I wanted to set to 0 the location_total's where there are no expense_out and also set the sync flag on true to indicate that the total is synchronized. I wanted to do it in only one query since the sync flag can be reset by a trigger activated when an expense_out is modified/added/removed for that location by another query and I wanted to keep the integrity (since the query is executed as a transaction, right?). But this integrity might be break anyway right after the query was executed so I can probably do it in a second query.
As for the EXLPAIN result (sorry for the bad table formating):
1. with INNER JOIN
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16896
1 PRIMARY location_total eq_ref PRIMARY,idx_id_location,idx_id_period PRIMARY 8 totals.id_location,totals.id_period 1
2 DERIVED expense_out ALL idx_id_location,id_period NULL NULL NULL 45453 Using temporary
2 DERIVED location_total eq_ref PRIMARY,idx_id_location,idx_id_period PRIMARY 8 impensa.expense_out.id_location,impensa.expense_ou... 1 Using where
2. with LEFT JOIN
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY location_total ALL NULL NULL NULL NULL 15570
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16896
2 DERIVED expense_out ALL idx_id_location,id_period NULL NULL NULL 32603 Using temporary
2 DERIVED location_total eq_ref PRIMARY,idx_id_location,idx_id_period PRIMARY 8 impensa.expense_out.id_location,impensa.expense_ou... 1 Using where
It seems that LEFT JOIN doesn't use keys as much as the INNER JOIN query so this might be an explanation, right?