Check if two values ​​in query are the same, making query very slow

Question

my goal:

Query, providing all provinces where buildings are under construction.

my question:

Once I make sure every building is compared to the same building (compare upgrade_id), the query goes on forever. Without the last part of the where statement it would take 1 second, which is totally fine.

Table settings

Provinces change from time to time and are saved daily with the current timestamp. The Province_has_building table contains many-to-many relationships from provinces to buildings. Each building has its upgrade_id -> building type and its health.

Inquire

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

explain

surface	type	Possible keys	key	refer to	OK	Filtered	additional
one	refer to	Main,fk_province_game1_idx	fk_province_game1_idx	constant	237387	100.00
a_pb	refer to	Main,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	Use index
a_b	eq_ref	Primary school, middle school	main	testing.a_pb.building_id	1	100.00
b_b	refer to	Primary school, middle school	Middle School	test.a_b.upgrade_id	9	100.00
b_pb	refer to	Main,fk_building_has_province_province1_idx,fk_building_has_province_building1_idx	main	testing.b_b.building_id	1026	100.00	Use index
b	refer to	main	main	testing.b_pb.province_id	1	5.00	place of use

Answer 1

Add some composite indexes:

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)

If province_has_building is a "many-to-many mapping table", please see many-to-many for more acceleration techniques.

For further discussion, please provide SHOW CREATE TABLE.

When adding a composite index, delete the index with the same leading column. That is, when you have both INDEX(a) and INDEX(a,b), discard the former.