MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize Inner Join With WHERE BETWEEN dates
Posted by: Rick James
Date: May 25, 2013 11:43AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Inner Join With WHERE BETWEEN dates
1482
May 25, 2013 11:43AM


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.