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

user_19562571 0
Viewed 31

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
mysql sql
1 Answers

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.

user_17668310
Related