Re: inner join vs left join - huge performance difference

[Peter Brawley]

Comma join syntax (select ... from a,b where ...) is not recommended in MySQL since version 5.0,12. Join...On syntax is recommended.

Left Join requires the query engine to read all data rows on the left side of the join. Your table is evidently big, and you're using datetime funcs that MySQL can't index. You might get a quick fix by adding a computed column for the 3-hour-incremented timestamp to your first query, saving the result as an intermediate table, adding an index on the columns to be compared, then picking off the desired rows from that.