MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Inner Join With WHERE BETWEEN dates
Posted by: Rick James
Date: May 22, 2013 06:41PM

AND a.ClockIn <> b.ClockOut
AND a.ClockOut <> b.ClockIn
AND ( a.ClockIn BETWEEN b.ClockIn AND b.ClockOut
OR a.ClockOut BETWEEN b.ClockIn AND b.ClockOut
)
-->
AND a.ClockIn < b.ClockOut AND a.ClockOut > b.ClockIn

That is much simpler than what you have, but, alas, may not be faster.
There is no simple way to optimize overlapping ranges.

Need to see SHOW CREATE TABLE.
SHOW VARIABLES LIKE '%buffer%';

rows: 3766 -- discouraging
Extra: Using where; Using index -- encouraging

rows: 82 -- discouraging
Extra: Using where; Using index -- encouraging

key_len: 5 -- Are the fields NULLable? Shouldn't they be NOT NULL?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Inner Join With WHERE BETWEEN dates
1582
May 22, 2013 06:41PM


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.