MySQL Forums
Forum List  »  Performance

Join by date range unacceptably slow
Posted by: Ed Trembicki-Guy
Date: May 15, 2017 02:48PM

Did I construct an inefficient UPDATE statement?

I am trying to update the column match_count, initially set to NULL, in table B with the count of matching records in table A. A record in table A is a match to a record in table B if the start and end times in table A overlap the start and end times in table B. This is the query statement I used:

UPDATE B
SET B.match_count = (
SELECT COUNT(*)
FROM A
WHERE A.start_time BETWEEN B.start_time and B.end_time
or B.start_time between A.start_time and A.end_time
)
WHERE B.match_count IS NULL
ORDER BY B.id
LIMIT 15000;

I had to set the 15000 limit and add the NULL test, so that it would run in under 10 minutes; otherwise the update would fail with a timeout. I created an index on each of A.start_time, A.end_time, B.start_time, B.end_time, A.start_time + A.end_time, B.start_time + B.end_time, B.match_count, but still the update takes what I thought to be an exceptionally long time.
Table A has 76,388 records and table B has 195,191 records.
Any insight is greatly appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Join by date range unacceptably slow
173
May 15, 2017 02:48PM


Sorry, only registered users may post in this forum.

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.