Re: Join by date range unacceptably slow
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.