檢查查詢中的兩個值是否相同，使查詢非常慢

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) 時，丟棄前者。