优化使用between子句的SQL [英] Optimize SQL that uses between clause

问题描述

请考虑以下2个表:

Table A:
id
event_time

Table B
id
start_time
end_time

表A中的每条记录都映射到表B中的正好1条记录.这意味着表B没有重叠的时间段.表A中的许多记录可以映射到表B中的同一记录.

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.

我需要一个查询，该查询返回所有A.id，B.id对.像这样:

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

我正在使用MySQL，但无法优化此查询.表A中有约980条记录，表B中有130.000条记录，这是永远的事.我知道必须执行980个查询，但是在功能强大的计算机上花费超过15分钟的时间却很奇怪.有什么建议吗?

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.我无法更改数据库架构，但是可以添加索引.但是，时间字段上的索引(包含1或2个字段)无济于事.

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.

推荐答案

您可能想尝试类似的东西

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

如果您在B的Start_Time，End_Time字段上有一个索引，那么它应该可以很好地工作.

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

这篇关于优化使用between子句的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！