KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
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.
Tags (comma-separated)
Save Edits
Cancel