Re: inner join vs left join - huge performance difference

[Peter Brawley]

What is the optimizer_switch value?

MySQL 5.6 and earlier give four output lines from either of your Join queries, 5.7 gives 2, are you using 5.7?

5.6 and 5.7 optimise subqueries better (eg they can do inside-outside rather than outside-in) than 5.5 and 5.1.

If a modern version of MySQL can't improve on the performance you are seeing, and if there are too many date parsing options for virtual columns to be practical, you can always fall back on saving q1 and q2 results, which Explain says are small, and extracting your result from them.



Edited 1 time(s). Last edit at 07/09/2016 09:53AM by Peter Brawley.