Schwindel bei 5fach SubSelect o.ä.

[ippurk]

Interessanter Artikel.

Habe meine DB-Struktur mal in folgendes umgebaut:

Tabelle 1 (flights):
flightID day_start starttime endtime departure arrival aircraft

Tabelle 2 (crewactions):
flightID crewmember

Mit folgender Abfrage bekomme ich alle Mitarbeiter und deren jeweilige Flüge:
[highlight=sql]
SELECT DISTINCT
b.cm
a.fid,
a.day_start,
a.start,
a.end,
a.dep,
a.arr
FROM flights a
INNER JOIN crewactions b ON a.fid = b.fid
INNER JOIN crewactions c ON b.fid = c.fid
WHERE
(
b.cm =
(
SELECT b.cm
FROM crewactions as b
WHERE c.cm = b.cm
AND b.fid = a.fid
)

)
ORDER BY b.cm, a.day_start, a.start
[/highlight]

Über crewactions fid und cm habe ich einen Index angelegt, der auch verwendet wird.
Überraschender Weise läuft diese Abfrage ziemlich schnell ab. Bin mir allerdings nicht so ganz sicher, ob ich das auch komplett verstanden habe, was ich da geschrieben habe. Kann man das noch verbessern ?

Und jetzt probiere ich schon seit zwei Tagen, aus diesem Ergebnis eben, wie oben schon diskutiert, pro Mitarbeiter immer die erste Startzeit des Tages, die letzte Ankunftszeit des Tages, den ersten Abflughafen und den letzten Ankunftsflughafen aufzulisten. Wie das grundsätzlich ginge, habe ich schon verstanden, ich weiß nur nicht, wie ich diese beiden Abfragen nun miteinander verbinden muss.

Hier nochmal die Abfrage, die ich für letzteres Problem nutze:
[highlight=sql]
SELECT
a.date as date,
a.start AS start,
b.end AS end,
a.dep AS dep,
b.arr AS arr,
a.reg AS reg,
addtime(
timediff(b.end,a.start),
addtime(
if((a.dep = 'TXL'),'01:15:00','01:00:00'),
if((b.arr = 'TXL'),'00:45:00','00:30:00')))
AS dutytime


FROM flights_per_cm a
JOIN flights_per_cm b
WHERE
(
(
a.start =
(
SELECT MIN(b.start)
FROM flights_per_cm b
WHERE b.date = a.date
)
)

AND
(
b.end =
(
SELECT MAX(b.end)
FROM flights_per_cm b
WHERE b.date = a.date
)
)
)
[/highlight]

Ich dachte ja, ich könnte in unterer Abfrage flights_per_cm einfach durch die obere Abfrage ersetzten,
das klappt allerdings irgendwie nicht.

[Falk Prüfer]

Hallo,

...Bin mir allerdings nicht so ganz sicher, ob ich das auch komplett verstanden habe, was ich da geschrieben habe. Kann man das noch verbessern ?

Sieht auf den ersten Blick etwas "doppeltgemoppelt" aus. Wofür benötigst du den zweiten Join auf crewactions? Auch die Where-Bedingungen scheinen mir nur Selbstzweck zu sein.
Nach meiner Vorstellung dürfte das hier reichen:
[highlight=sql]
SELECT b.cm, a.fid, a.day_start,
a.start, a.end, a.dep, a.arr
FROM flights a
INNER JOIN crewactions b ON a.fid = b.fid
ORDER BY b.cm, a.day_start, a.start
[/highlight]

Zu deinem Anderen Problem: Erste Startzeit des Tages und letzte Ankunftszeit des Tages sollten noch relativ einfach zu lösen sein, mit einer einfachen Gruppierung:
[highlight=sql]
SELECT b.cm, a.day_start,
min(a.start) erste_startzeit, max(a.end) letzte_landezeit
FROM flights a
INNER JOIN crewactions b ON a.fid = b.fid
group BY b.cm, a.day_start
[/highlight]
Für die zugehörigen Flughäfen müssen dann die passenden Flüge nochmal hinzugejoint werden, wobei die Kriterien das Crewmitglied und die jeweilige Lande-/Startzeit sind.

Gruß Falk

[ippurk]

Also, es funktioniert zwar, sieht aber etwas umständlich aus:

[highlight=sql]
SELECT
a.cm,
a.day_start,
a.letzte_landezeit,
a.erste_startzeit,
b.dep,
c.arr

FROM
(
SELECT
b.cm,
a.day_start,
min(a.start) erste_startzeit,
max(a.end) letzte_landezeit
FROM flights a INNER JOIN crewactions b ON a.fid = b.fid
GROUP BY b.cm, a.day_start
) AS a
LEFT JOIN (SELECT fa.start, fa.dep, ca.cm
FROM flights AS fa INNER JOIN crewactions AS ca ON fa.fid = ca.fid)
AS b ON b.start = erste_startzeit AND b.cm = a.cm
LEFT JOIN (SELECT fb.end, fb.arr, cb.cm
FROM flights AS fb INNER JOIN crewactions AS cb ON fb.fid = cb.fid)
AS c ON c.end = letzte_landezeit AND c.cm = a.cm

ORDER BY a.cm, a.day_start
[/highlight]

[Falk Prüfer]

Also, es funktioniert zwar, sieht aber etwas umständlich aus:...

Naja, a) wenns einfach wäre könnte es jeder und b) haben komplexe Abfragen eben auch komplexe Statements zur Folge.

Zur (optischen) Vereinfachung könnte man für das grundlegende Statement (Join aus Flügen und Crew) eine VIEW anlegen und dann im Gesamtstatement diese View verwenden. Einfacher im Sinne der Ausführung wir es dadurch natürlich nicht.

Gruß Falk

[ippurk]

ähm, das mit den Views habe ich schon gemacht, wird dadurch auch ein wenig schneller, aber: Habe ich das denn so richtig gemacht mit diesen zusätzlichen JOINS ?

EDIT: Die Flüge pro Mitarbeiter und Tag aufzulisten, dauert unter einer Sekunde und irgendwie finde ichs dann etwas frustrierend, daß die Zuordnung von Abflugs- und Ankunftsflughafen das ganze auf ca. 20 Sekunden Abfragezeit verlängert. So kompliziert kann dieser letzte Schritt doch eigentlich gar nicht sein...

[Falk Prüfer]

ähm, das mit den Views habe ich schon gemacht, wird dadurch auch ein wenig schneller, aber: Habe ich das denn so richtig gemacht mit diesen zusätzlichen JOINS ?

Ja, so in etwa hatte ich mir das gedacht...
Vlt. ist ja ein INNER statt einem LEFT JOIN besser geeignet. Einen LEFT JOIN dürftest du eigentlich nicht brauchen. Runtergekommen sind sie bisher alle...

[ippurk]

Die Frage hatte ich mir auch schon gestellt, allerdings wird die Abfrage auch hier etwa 10 Sekunden schneller, wenn ich einen Left statt einem Inner Join nehme. Keine Ahnung, woran das jetzt liegt.

[Falk Prüfer]

...allerdings wird die Abfrage auch hier etwa 10 Sekunden schneller, wenn ich einen Left statt einem Inner Join nehme. Keine Ahnung, woran das jetzt liegt.

Das ist eher ungewöhnlich und man sollte sich mal das konkrete Statement mit EXPLAIN ansehen, um zu sehen wo da der Knackpunkt liegt.

Gruß Falk

[ippurk]

34.469 sec mit INNER JOIN
20.640 sec mit LEFT JOIN

die beiden EXPLAIN Ausgaben:

[ippurk]

was ich allerdings nicht verstehe, daß ich für die Zuordnung der Ab- und Ankunftsflughäfen diese wahnsinnigen Zusatzjoints brauche. Eigentlich sollte man doch sowas sagen können wie, da wo min(start) auftaucht, auch die Spalte dep auslesen.

[Falk Prüfer]

...Eigentlich sollte man doch sowas sagen können wie, da wo min(start) auftaucht, auch die Spalte dep auslesen.

Wenn du so eine Lösung findest, werden dir viele Leute sehr dankbar sein

Gruß Falk

P.S.: Joints sind zwar auch nicht jedermanns Sache, aber für SQL benötigt man sie ganz und gar nicht ...

[ippurk]

[highlight=sql]
SELECT
a.cm,
a.day_start,
a.erste_startzeit,
a.letzte_landezeit,
(
SELECT
dep
FROM cms_flights
WHERE start = erste_startzeit AND cms_flights.cm = a.cm
) AS dep,
(
SELECT
arr
FROM cms_flights
WHERE end = letzte_landezeit AND cms_flights.cm = a.cm
) AS arr
FROM
umlaufe_cms AS a



ORDER BY a.cm, a.day_start
[/highlight]

cms_flights ist ein view, bei dem die flüge auf die cms gejoint werden und umlaufe_cms fasst diesen view als Tagesdienste zusammen. Das ganze liefert prinzipiell das gleiche Ergebniss, dauert aber nur 3 Sekunden. Allerdings, wenn ich da jetzt noch einen "Überselect" mache, um die Tagesdienstzeit auszurechnen, dauerts wieder 20 Sekunden. Leider ist in der Oberabfrage dep und arr irgendwie nicht bekannt, wenn ich die Zeitberechnung direkt da einbaue.

Hier der "Überselect":
[highlight=sql]
SELECT
b.cm,
b.day_start,
b.erste_startzeit,
b.letzte_landezeit,
b.dep,
b.arr,
addtime(
timediff(b.letzte_landezeit, b.erste_startzeit),
addtime(
if((b.dep = 'TXL'),'01:15:00','01:00:00'),
if((b.arr = 'TXL'),'00:45:00','00:30:00')))
AS dutytime
FROM
(
SELECT
a.cm,
a.day_start,
a.erste_startzeit,
a.letzte_landezeit,
(SELECT dep
FROM cms_flights
WHERE start = erste_startzeit AND cms_flights.cm = a.cm) AS dep,
(SELECT arr
FROM cms_flights
WHERE end = letzte_landezeit AND cms_flights.cm = a.cm) AS arr
FROM
(
SELECT
a.cm,
a.day_start,
min(a.start) erste_startzeit,
max(a.end) letzte_landezeit

FROM cms_flights a
GROUP BY a.cm, a.day_start
) AS a
) AS b

ORDER BY b.cm, b.day_start
[/highlight]

Naja, anscheinend bin ich nicht der einzige mit diesem Problem...

[ippurk]

Also, hab gerade festgestellt, daß meine unterschiedlichen Abfragezeiten wohl mit dem bei Workbench standartmäßig gesetzten Limit von 1000 zusammenhängen. Zwar geht in oben genanntem Beispiel die "Duration" deutlichschneller aber die Fetchtime steigt stark an, ach egal, weiß auch nicht, woran das jetzt nun wieder liegt.