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

[Clint Byrum]

Yes your EXPLAIN clearly confirms what I was saying.

With the INNER join, the first step is to build and scan the derived table from the subselect. Next it joins this to location_total with the primary key, which should be very fast (might even faster if you ordered your subselect by it.. as InnoDB tables are clustered on their primary key).

In the left join, as I said, it has to do all the records in location_total, and all the records in derived 2. This is orders of magnitude slower, as you've seen!