检查查询中的两个值是否相同，使查询非常慢

Question

我的目标：

查询，提供正在建设建筑物的所有省份。

我的问题：

一旦我确保每个建筑物都与同一建筑物进行比较（比较upgrade_id），查询就会永远持续下去。如果没有 where 语句的最后一部分，则需要 1 秒，这完全没问题。

表格设置

省份会不时发生变化，并且每天都会使用当前时间戳进行保存。 Province_has_building 表包含从省份到建筑物的多对多关系。每个建筑物都有其upgrade_id -> 建筑物类型及其健康状况。

查询

SELECT 
a.province_id,
a.province_location_id, 
a.current_time, 
b.current_time,
a_b.upgrade_id, 
b_b.upgrade_id, 
(a_b.health - b_b.health) as health
FROM province a
JOIN province b
ON b.province_location_id = a.province_location_id and b.current_time between TIMESTAMP(DATE_SUB(a.current_time, INTERVAL 3600 * 24 + 500 SECOND)) and TIMESTAMP(DATE_SUB(a.current_time, INTERVAL 3600 * 24 - 500 SECOND))

-- Day 1 Building
JOIN province_has_building a_pb
on a_pb.province_id = a.province_id
JOIN building a_b
on a_pb.building_id = a_b.building_id


-- Day 2 Building
JOIN province_has_building b_pb
on b_pb.province_id = b.province_id
JOIN building b_b
on b_pb.building_id = b_b.building_id                                                                            

WHERE a.game_id = 5547382 and a_b.upgrade_id = b_b.upgrade_id

解释

表	类型	可能的键	键	参考	行	已过滤	额外
一个	参考	主要，fk_province_game1_idx	fk_province_game1_idx	常量	237387	100.00
a_pb	参考	主要，fk_building_has_province_province1_idx，fk_building_has_province_building1_idx	fk_building_has_province_province1_idx	testing.a.province_id	1	100.00	使用索引
a_b	eq_ref	小学，中学	主要	testing.a_pb.building_id	1	100.00
b_b	参考	小学，中学	中学	测试.a_b.upgrade_id	9	100.00
b_pb	参考	主要，fk_building_has_province_province1_idx，fk_building_has_province_building1_idx	主要	testing.b_b.building_id	1026	100.00	使用索引
b	参考	主要	主要	testing.b_pb.province_id	1	5.00	使用地点

Answer 1

添加一些复合索引：

province:  INDEX(game_id, province_location_id,  province_id, current_time)
province:  INDEX(province_location_id,  current_time, province_id)
building:  INDEX(upgrade_id, building_id,  health)
province_has_building:  INDEX(province_id,  building_id)

如果 province_has_building 是“多对多映射表”，请参见 多对多了解更多加速技巧。

如需进一步讨论，请提供SHOW CREATE TABLE。

添加复合索引时，删除具有相同前导列的索引。也就是说，当你同时拥有 INDEX(a) 和 INDEX(a,b) 时，丢弃前者。