Re: Optimize Inner Join With WHERE BETWEEN dates

[Rick James]

You have at least 40GB of ram? If not, then this is too large:
innodb_buffer_pool_size | 34359738368

KEY `idx_overlappcheck` (`UserID`,`TimeClass`,`RecordID`,`ClockIn`,`ClockOut`),
-->
KEY `idx_overlappcheck` (`UserID`,`TimeClass`,`ClockIn`,`ClockOut`,`RecordID`),

This part can't make much use of the index:
> AND a.RecordID != b.RecordID
Whereas, the reformulation I suggested can make some use of the revised index.

IN optimization -- That has changed over the years. Which version of MySQL are you using?
Please try this:
Run the same select, but with UserID = constant
(and repeat for each of the ids you have).
If the sum of those times is faster than using the IN, then switch to
SELECT ... UserID = const1
UNION ALL
SELECT ... UserID = const1
UNION ALL
...

I believe (without proof) that version 5.6 will do that sort of optimization for you, but 4.x might not. I am unsure about 5.0 and 5.1.

The issue has to do with 'leapfrogging' through the index -- based on the first field being UserID IN (...) versus a brute force scan from the min value in the IN to the max value. In 5.6, I think it is called MRR.

I thought about PARTITIONing, but don't envision much help there.