Vérifiez si deux valeurs dans la requête sont identiques, ce qui rend la requête très lente

Question

Mon objectif :

Requête, fournissant toutes les provinces où des bâtiments sont en construction.

Ma question :

Une fois que je m'assure que chaque bâtiment est comparé au même bâtiment (comparez Upgrade_id), la requête continue indéfiniment. Sans la dernière partie de l'instruction Where, cela prendrait 1 seconde, ce qui est tout à fait correct.

Paramètres de la table

Les provinces changent de temps en temps et sont enregistrées quotidiennement avec l'horodatage actuel. La table Province_has_building contient des relations plusieurs-à-plusieurs entre les provinces et les bâtiments. Chaque bâtiment a son Upgrade_id -> type de bâtiment et sa santé.

Requête

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

Explication

Table	Type	Clés possibles	Clé	Référence	OK	Filtré	Extra
un	Référence	Principal, fk_province_game1_idx	fk_province_game1_idx	Constante	237387	100.00
a_pb	Référence	Principal, fk_building_has_province_province1_idx, fk_building_has_province_building1_idx	fk_building_has_province_province1_idx	test.a.province_id	1	100.00	Utiliser l'index
a_b	eq_ref	École primaire, collège	Principal	testing.a_pb.building_id	1	100.00
b_b	Référence	École primaire, collège	Collège	test.a_b.upgrade_id	9	100.00
b_pb	Référence	Principal, fk_building_has_province_province1_idx, fk_building_has_province_building1_idx	Principal	testing.b_b.building_id	1026	100.00	Utiliser l'index
b	Référence	Principal	Principal	testing.b_pb.province_id	1	5h00	Lieu d'utilisation

Answer 1

Ajoutez quelques index composés :

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)

Si province_has_building est une "table de mappage plusieurs-à-plusieurs", voir Many-to-Many pour plus de conseils d'accélération.

Pour une discussion plus approfondie, veuillez fournirSHOW CREATE TABLE.

Lors de l'ajout d'un index composite, supprimez les index avec la même colonne principale. Autrement dit, lorsque vous avez à la fois INDEX(a) et INDEX(a,b), supprimez le premier.