MySQL Forums
Forum List  »  Optimizer & Parser

Re: inner join vs left join - huge performance difference
Posted by: Peter Brawley
Date: July 06, 2016 02:32PM

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.

Options: ReplyQuote

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.