Re: inner join vs left join - huge performance difference
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.
Subject
Views
Written By
Posted
12200
July 06, 2016 09:32AM
2896
July 06, 2016 12:00PM
1940
July 06, 2016 12:18PM
1570
July 06, 2016 12:29PM
1407
July 06, 2016 12:36PM
1464
July 06, 2016 12:44PM
1435
July 06, 2016 01:51PM
Re: inner join vs left join - huge performance difference
1434
July 06, 2016 02:32PM
1168
July 06, 2016 02:41PM
1289
July 08, 2016 11:58PM
1451
July 09, 2016 09:54AM
1553
September 27, 2016 07:13AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.