Checking if two values in Query are the same, makes Query very slow

My Goal:

A Query, which provides all provinces, in which a building is constructing.

My Problem:

As soon as I make sure that every building is compared to the same building (comparing upgrade_id) the Query takes forever. Without the last part of the where statement it takes 1 sec, which is totally fine.

Table Setup

Provinces change from time to time and are saved every day with their current timestamp. The province_has_building table contains the many to many relationship from provinces to buildings. Every building has their upgrade_id -> type of building and their health.

Query

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

table	type	possible_keys	key	ref	rows	filtered	extra
a	ref	PRIMARY,fk_province_game1_idx	fk_province_game1_idx	const	237387	100.00
a_pb	ref	PRIMARY,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	Using index
a_b	eq_ref	PRIMARY, SECONDARY	PRIMARY	testing.a_pb.building_id	1	100.00
b_b	ref	PRIMARY, SECONDARY	SECONDARY	testing.a_b.upgrade_id	9	100.00
b_pb	ref	PRIMARY,fk_building_has_province_province1_idx,fk_building_has_province_building1_idx	PRIMARY	testing.b_b.building_id	1026	100.00	Using index
b	ref	PRIMARY	PRIMARY	testing.b_pb.province_id	1	5.00	Using where

Answer

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”, see Many-to-many for more speed-up tips.

For further discussion, please provide SHOW CREATE TABLE.

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.