MySQL Forums
Forum List  »  Performance

Re: Join by date range unacceptably slow
Posted by: Ed Trembicki-Guy
Date: May 18, 2017 07:32AM

Hi Øystein,

Thank you for your suggestions. I also mentioned it to a colleague who recalled a similar problem, and after rifling through some old notes (actual Post-it notes!), he came across this:

UPDATE B
SET B.match_count = (
SELECT COUNT(*)
FROM A
WHERE A.start_time <= B.end_time
and B.start_time <= A.end_time
)

The good news is that it processed the entire set of 195,191 records in a single update statement.
The bad news is that the duration was 7802.719 seconds (about 2 hours, 10 minutes)

The explain plan reported the following:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY B index PRIMARY,ix_start,ix_end,ix_start_end,ix_end_start,ix_match_count PRIMARY 4 192337
2 DEPENDENT SUBQUERY A ALL ix_start,ix_end,ix_start_end,ix_end_start 76650 Range checked for each record (index map: 0xF)

I'm wondering if adding a hint might have any effect.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Join by date range unacceptably slow
1008
May 18, 2017 07:32AM


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.