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.