Überprüfen Sie, ob zwei Werte in der Abfrage gleich sind, wodurch die Abfrage sehr langsam wird

Question

Mein Ziel:

Abfrage mit Angabe aller Provinzen, in denen sich Gebäude im Bau befinden.

Meine Frage:

Sobald ich sichergestellt habe, dass jedes Gebäude mit demselben Gebäude verglichen wird (vergleiche upgrade_id), wird die Abfrage ewig fortgesetzt. Ohne den letzten Teil der where-Anweisung würde es 1 Sekunde dauern, was völlig in Ordnung ist.

Tabelleneinstellungen

Provinzen ändern sich von Zeit zu Zeit und werden täglich mit dem aktuellen Zeitstempel gespeichert. Die Tabelle Province_has_building enthält Viele-zu-Viele-Beziehungen von Provinzen zu Gebäuden. Jedes Gebäude hat seine upgrade_id -> Gebäudetyp und seinen Zustand.

Abfrage

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

Erklärung

Tisch	Typ	Mögliche Schlüssel	Schlüssel	Referenz	OK	Gefiltert	Extra
eins	Referenz	Main, fk_province_game1_idx	fk_province_game1_idx	Konstant	237387	100,00
a_pb	Referenz	Main, fk_building_has_province_province1_idx, fk_building_has_province_building1_idx	fk_building_has_province_province1_idx	Testen einer Provinz-ID	1	100,00	Index verwenden
a_b	eq_ref	Grundschule, Mittelschule	Haupt	testing.a_pb.building_id	1	100,00
b_b	Referenz	Grundschule, Mittelschule	Mittelschule	test.a_b.upgrade_id	9	100,00
b_pb	Referenz	Main, fk_building_has_province_province1_idx, fk_building_has_province_building1_idx	Haupt	testing.b_b.building_id	1026	100,00	Index verwenden
b	Referenz	Haupt	Haupt	testing.b_pb.province_id	1	5.00	Verwendungsort

Answer 1

添加一些复合索引：

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)

如果 province_has_building 是“多对多映射表”，请参见 多对多了解更多加速技巧。

如需进一步讨论，请提供SHOW CREATE TABLE。

添加复合索引时，删除具有相同前导列的索引。也就是说，当你同时拥有 INDEX(a) 和 INDEX(a,b) 时，丢弃前者。