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 |
Advertisement
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.