Optimierung für Abfragen mit JOIN

[micromumpitz]

Hallo,

nachdem mir hier in letzter Zeit mehrere ganz hervorragende Tipps gegeben worden sind , möchte ich euch noch mal mit 2 Fragen behelligen, von denen ich hoffe, dass mir jemand dabei helfen kann.

Teilfrage 1
Angenommen ich habe zwei Tabellen wie die folgenden:

Nun möchte ich die Anzahl der Sachen abfragen, die das Datum 02.05.2009 tragen und die einer Kategorie zugeordnet sind, die als "essbar" eingestuft ist. Dazu verwende ich die folgende Abfrage, die auch wunschgemäß funktioniert:

SELECT COUNT(s.katid) FROM sachen s
JOIN (SELECT katid, essbar FROM kategs) AS k
ON k.katid=s.katid
WHERE DAY(s.datum)= '2' AND MONTH(s.datum)='5' AND YEAR(s.datum)='2009' AND k.essbar='1'

Allerdings habe ich den Eindruck, dass die Abfrage in Anbetracht einer recht geringen Datenmenge in den Tabellen ziemlich lange dauert. Gäbe es da vielleicht Möglichkeiten zur Optimierung? s.datum ist übrigens vom Typ DATETIME und speichert neben dem Datum auch noch eine Uhrzeit, die für diese Abfrage aber nicht von Bedeutung ist. Es geht hier nur um den Tag, daher die umständliche Schreibweise mit DAY(...) usw. k.essbar ist vom Typ TINYINT und entweder 0 oder 1.

Teilfrage 2
Neben dieser Abfrage habe ich noch eine zweite, bei der der Optimierungsbedarf noch größer zu sein scheint. Die Ausführung der Abfrage dauert rund 30 Sekunden, obwohl der PC gar nicht mal langsam ist und das würde ich gerne noch schneller hinbekommen.
Die Tabellen sind wieder die gleichen wie oben, nur dass hier noch eine dritte dazukommt:

In diesem Fall möchte ich gerne abfragen, wie viele Sachen am 02.05.2009 gegessen worden sind, die nicht essbar waren... ^^
Dazu verwende ich bisher die folgende Abfrage:

SELECT COUNT(*) FROM verputzt v
WHERE DAY(v.datum)= '2' AND MONTH(v.datum)='5' AND YEAR(v.datum)='2009'
AND v.sachid IN
 (SELECT s.sachid FROM sachen s
   JOIN (SELECT katid, essbar FROM kategs) AS k
   ON k.katid=s.katid
   WHERE k.essbar='0'
 )

Ich glaube das Problem ist, dass für jeden Datensatz der Tabelle verputzt die Unterabfrage nach dem IN ausgeführt wird oder? Das wäre ja eigentlich nur 1x nötig. :-?

Gruß und danke schon im Voraus!
MicroMumpitz

[AmicaNoctis]

Hallo,

Joins auf Subselects sind auch eher nicht so optimal, weil MySQL damit gezwungen wird, den Subselect ohne Hinblick auf den Kontext und damit ohne Optimierungsmöglichkeiten abzuhandeln, das Ergebnis zwischenzuspeichern und dann erst zu joinen. Wenn du sowieso ein konkretes Datum prüfst, warum dann nicht gleich komplett, sondern in Teilstücken?

Teilfrage 1:

SELECT COUNT(*) FROM sachen AS s
JOIN kategs AS k ON k.katid=s.katid
WHERE s.datum = '2009-05-02' AND k.essbar='1'

(nicht getestet)

Teilfrage 2:

SELECT COUNT(*) FROM verputzt AS v
JOIN sachen AS s ON s.sachid=v.sachid
JOIN kategs AS k ON k.katid=s.katid
WHERE v.datum = '2009-05-02' AND k.essbar='0'

(nicht getestet)

Gruß,

Anja

[micromumpitz]

Hallo Anja,

vielen Dank für die äußerst schnelle und hilfreiche Antwort!

Wenn du sowieso ein konkretes Datum prüfst, warum dann nicht gleich komplett, sondern in Teilstücken?

Stimmt, ich könnte statt der einzelnen Überprüfung von DAY, MONTH und YEAR auch mit DATE(...) den Datumsteil aus den DATETIME-Spalten extrahieren. Damit lässt sich vielleicht auch noch mal Zeit gewinnen.

Es funktioniert übrigens - obwohl ungetestet - perfekt! Danke nochmals!

Gruß,
MicroMumpitz