Optimize SQL that uses between clause

Asked 2009-02-17T15:41:25.090

Consider the following 2 tables:

Table A:
id
event_time

Table B
id
start_time
end_time

Every record in table A is mapped to exactly 1 record in table B. This means table B has no overlapping periods. Many records from table A can be mapped to the same record in table B.

I need a query that returns all A.id, B.id pairs. Something like:

SELECT A.id, B.id 
FROM A, B 
WHERE A.event_time BETWEEN B.start_time AND B.end_time

I am using MySQL and I cannot optimize this query. With ~980 records in table A and 130.000 in table B this takes forever. I understand this has to perform 980 queries, but taking more than 15 minutes on a beefy machine is strange. Any suggestions?

P.S. I cannot change the database schema, but I can add indexes. However an index (with 1 or 2 fields) on the time fields doesn't help.

sql mysql query-optimization

Edit Report

2 Answers

You may want to try something like this

Select A.ID,
(SELECT B.ID FROM B
WHERE A.EventTime BETWEEN B.start_time AND B.end_time LIMIT 1) AS B_ID
FROM A

If you have an index on the Start_Time,End_Time fields for B, then this should work quite well.

answered 2009-02-17T16:14:13.250

The only way out you have to speed up the execution of this query is by making use of indexes.

Take care to put into an index your A.event_time and then put into another index B.start_time and B.end_time.

If as you said this is the only one condition which binds the two entities together, I think this is the only solution you can take.

Fede

answered 2009-02-17T16:11:57.297

Optimize SQL that uses between clause

2 Answers

Your Answer