MySQL Forums
Forum List  »  Performance

Re: Join by date range unacceptably slow
Posted by: Øystein Grøvlen
Date: May 16, 2017 01:49AM

Hi Ed,

I think you are hitting two issues that make your query take long:

1. Traditional indexes are not particularly efficient for expressions like "15 BETWEEN a AND b" where a and b are columns. (See http://jorgenloland.blogspot.co.uk/2011/09/tips-and-tricks-killer-response-time.html#more)

2. MySQL does not handle OR expressions efficiently. Often you will get better performance by splitting such queries into two separate queries. In your case, you could try something like:

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

Given an index on A.start_time, the first subquery should be pretty efficient, while the second subquery will take some time, as discussed in the blog post I referred to above. However, a covering index on A(start_time, end_time) should help a bit.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Join by date range unacceptably slow
36
May 16, 2017 01:49AM


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.