langsame Query (join, where, group)

T

TanjaP

Benutzer
Beiträge
6
Hallo und ein frohes Neues Jahr erst einmal ;-)

Ich habe eine Datenbank für Hunde, in der auch die jeweiligen Züchter / Besitzer erfasst sind.
Die Datenbank hat 26000 Einträge für Hunde und knapp 4000 für Besitzer/Züchter. In der Hunde sind u.a. die Felder breeder_id und owner_id welche dann jeweils die id in der owner tabelle sind.

Nun möchte ich eine Abfrage starten, in der alle aktiven Züchter gelistet sind - dazu benötige ich
a.) alle Züchter (breeder) die in den letzten 8 Jahren einen Wurf hatten (Geburtsdatum des Hundes)
b.) alle Besitzer (owner), die eine Hündin bis zu 8 Jahren mit Zuchtzulassung haben
c.) alle Züchter, die aktuell ein Wurf (geplant) haben
d.) das ganze nach den Ländern sortiert (ist in der Tabelle owner in einer Spalte hinterlegt)


Ich hoffe ihr könnt mir folgen :confused:

Meine query dazu ist zu langsam, nur für Deutschland benötigt sie 8 sekunden - also mach ich was falsch ...


SELECT
d.id AS dogid, d.breeder_id, d.date_of_birth, d.gender, d.owner_id, d.breeder_id, d.breeding_approval_since,
h.id AS dogid, h.breeder_id, h.date_of_birth, h.gender, h.owner_id, h.breeder_id, h.breeding_approval_since,

MAX(YEAR(h.date_of_birth)) AS lastlitter,
owner.id, owner.kennelname, owner.country, owner.country_short, owner.kennel_note,
puppy.breeder_id,
MAX(puppy.sollgeboren) AS birthday,
DATE_ADD(MAX(puppy.sollgeboren), INTERVAL 84 DAY) AS database_dateadd,
DATE_SUB(MAX(puppy.sollgeboren), INTERVAL 60 DAY) AS database_datesub,
puppy.active AS showing
FROM owner
LEFT JOIN dog d ON d.owner_id = owner.id
LEFT JOIN dog h ON h.breeder_id = owner.id
LEFT JOIN puppy ON (owner.id = puppy.breeder_id AND puppy.active='1')


WHERE
(
d.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
AND d.breeding_approval_since !='0000'
AND owner.kennelname !=''
AND owner.kennel_note =''
AND owner.country_short='de'
)
or
(
h.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
AND owner.kennelname !=''
AND owner.kennel_note =''
AND h.breeder_id = owner.id
AND owner.country_short='de'
)


GROUP BY country_short, kennelname

ORDER BY kennelname ASC


Index ist keiner drin ....
Tipps, wie ich das ganze besser machen kann?
 
Werbung:
Dein Query hat durchaus Schwachpunkte.

1) Dein Select enthält gleichnamige Spalten, das ist nicht besonders elegant und dadurch kannst du eigentlich keine Auswertung aus dem Ergebnis machen.
2) Dein Select enthält allgemein viele Spalten und liefert somit viel Information. Es kann also eventuell ein Netzwerkproblem sein, auch wenn ich das jetzt nicht glaube.
3) Nicht alle Spalten im Select werden gruppiert oder aggregiert, du arbeitest aber mit GROUP BY. Das ist nur in älteren MySQL Versionen ohne Fehlermeldung möglich und syntaktisch falsch. Außerdem kann man keine Aussage über die Performance treffen wenn man nicht weiß was die DB da gruppiert. Ich denke auch das GROUP BY ist hier erstmal der letzte Schritt, erstmal möchte man die Rohdaten korrekt ermitteln, das könnte man also auch schachteln.
4) Dein WHERE dürfte die Performance fressen, vor allem 2 große AND Blöcke mit dem OR, das ist schon etwas aufwendig. EXPLAIN könnte hier die Kosten gut zeigen und ein Index wird vermutlich helfen aber erstmal sollte das Querry optimiert werden.
5) Deine Joins führen sehr viele Daten zusammen. Welche Daten brauchst du denn überhaupt? Wenn du nur Züchter nach Ländern gruppieren willst, warum joinst du dann alle Hunde-Informationen dazu und wirfst sie dann, bildlich gesprochen, im GROUP BY wieder weg? Es gibt mehrere Wege das ganze aufzuziehen, Joins sind einer davon. Aber erstmal solltest du das Ergebnis genauer definieren, das wäre glaube ich hilfreich.
 
Ok, ich benötige alle Züchter die noch aktiv sind - d.h. dazu benötige ich Daten der Hunde (hat der Züchter in den letzten 8 Jahren einen Wurf gehabt (kommt irgendwo in der Hundetabelle mit diesem Züchter ein Geburtsdatum in den letzten 8 Jahren vor - breeder_id)) und / oder hat diese Person eine Hündin die zuchtfähig ist (unter 8 Jahre und Zuchtzulassung - owner_id). Nicht dazu gehören dann z.B. Züchter, die vor 10 Jahren einen Wurf hatten ...
Die tabelle puppy dient für aktuelle Welpen (Zeitraum x Tage vor/nach errechnetem Geburtsdatum).
Es gibt also Personen, die haben einen Wurf gehabt und/oder haben eine Hündin die zuchtfähig ist oder Personen die nur eine zuchtfähige Hündin haben. Jede diese Konstellationen kann einen Wurf erwarten ....

Das ganze muss dann noch nach Ländern gruppiert werden ....
 
Das Grundproblem ist, wie schon genannt, daß die Abfrage vollkommen für den Eimer ist, da syntaktisch falsch. Daß MySQL in alten Versionen dennoch keinen Fehler meldet liegt einfach nur daran, daß es den Fehler nicht erkennt. Aller anderen DB-Systeme und aktuelle MySQL-Versionen würden die Abfrage nicht ausführen. Wirf das also weg und mache es neu & richtig.

Wir brauchen hier also nicht weiter über die Performance reden, denn das ist völlig zweitrangig.

Wenn Du zu Performance-Problemen dann mit einer richtigen Abfrage kommst:

  • schaue Dir das Explain an. Halte dazu im Falle von MySQL aber eine Folientüte bereit.
  • Spalten, die im Where vorkommen, profitieren von einem Index
  • Spalten, die im JOIN vorkommen, ebenfalls
  • falls Du korrekt mit Primary Keys / Foreign Keys arbeiten solltest, sind bereits die Indexe auf den PK's vorhanden
 
Danke für die Antwort - aber sorry, ich kann damit nichts anfangen :(

Was ist falsch und welche MySQL Version ist alt?
Und was ist eine Folientüte?

Das die Abfrage so nicht gut ist, weiß ich selber....

Stoßt mich mal (vielleicht anhand eines Beispieles) mit der Nase drauf, wie es syntaktisch korrekt wäre, bitte
 
TanjaP schrieb:
Danke für die Antwort - aber sorry, ich kann damit nichts anfangen :(

Was ist falsch und welche MySQL Version ist alt?
Zum Vergrößern anklicken....

@ukulele sagte es schon: alle Spalten im Result müssen entweder aggregiert oder gruppiert sein. Ansonsten ist es schlicht logisch falsch. Und fast alle MySQL-Versionen erkennen es nicht, wenn man diese Regel verletzt. Als Ergebniss bekommt man ein falsches.

Und was ist eine Folientüte?
Zum Vergrößern anklicken....

Kotzbeutel besser verständlich? Das Explain von MySQL ist schlicht. Sehr schlicht. Es sagt nicht viel aus. Im Vergleicht zu anderen Datenbanken kann man es schlicht vergessen, so schlicht ist es. MySQL hat kein Kostenmodell, keinen sinnvollen Planner, keine sinnvollen Pläne, nix. Das macht einfach sturr irgendetwas, was es kann. Und das ist nicht viel. Es kann z.B. viele Indexgeschichten (funktionale, partielle) Indexe nicht, es kennt keine GIN, GiST, BRIN und viele andere Indexe nicht, es kennt keine Tablespaces und keine Kostenparameter für unterschidliche Tablespaces nicht. Liste kann beliebig fortgesetzt werden.

Andreas
 
Hier mal drei grundsätzliche Wege wie man das anfangen kann, es gibt noch andere und es läßt sich natürlich auch kombinieren.

A)
Code: 
SELECT   o.country_short,o.kennelname
FROM   owner o
LEFT JOIN dog d
ON     d.owner_id = o.id
AND     d.date_of_birth >= DATE_SUB(CURDATE(),INTERVAL 8 YEAR)
AND     d.breeding_approval_since !='0000'
LEFT JOIN dog h
ON     h.breeder_id = o.id
AND     h.date_of_birth >= DATE_SUB(CURDATE(),INTERVAL 8 YEAR)
LEFT JOIN puppy p
ON     o.id = p.breeder_id
AND     p.active = '1'
WHERE   o.kennelname !=''
AND     o.kennel_note =''
AND     o.country_short='de'
AND   (   d.owner_id IS NOT NULL
OR     h.breeder_id IS NOT NULL
OR     p.breader_id IS NOT NULL )
ORDER BY o.country_short,o.kennelname
Prinzipiell deine Lösung nur habe ich etwas aufgeräumt und umgestellt.

B)
Code: 
SELECT   o.country_short,o.kennelname
FROM   owner o
WHERE   o.kennelname !=''
AND     o.kennel_note =''
AND     o.country_short='de'
AND   (   o.id IN (

SELECT   d.owner_id
FROM   dog d
AND     d.date_of_birth >= DATE_SUB(CURDATE(),INTERVAL 8 YEAR)
AND     d.breeding_approval_since !='0000'

     )
OR     o.id IN (

SELECT   h.breeder_id
FROM   dog h
AND     h.date_of_birth >= DATE_SUB(CURDATE(),INTERVAL 8 YEAR)
     )
OR     o.id IN (

SELECT   p.breeder_id
FROM   puppy p
WHERE   p.active = '1'

     ))
ORDER BY o.country_short,o.kennelname
Eine andere Vorgehensweise ohne Join vieler Daten. Je nach DBMS ermutlich unterschiedlich schnell. Man könnte auch statt IN ein NOT EXISTS und eine weitere Bedingung im Subselect verwenden.

C)
Code: 
SELECT   DISTINCT t.country_short,t.kennelname
FROM   (

SELECT   o.country_short,o.kennelname
FROM   owner o
INNER JOIN dog d
ON     d.owner_id = o.id
AND     d.date_of_birth >= DATE_SUB(CURDATE(),INTERVAL 8 YEAR)
AND     d.breeding_approval_since !='0000'
WHERE   o.kennelname !=''
AND     o.kennel_note =''
AND     o.country_short='de'
UNION ALL
SELECT   o.country_short,o.kennelname
FROM   owner o
INNER JOIN dog h
ON     h.breeder_id = o.id
AND     h.date_of_birth >= DATE_SUB(CURDATE(),INTERVAL 8 YEAR)
WHERE   o.kennelname !=''
AND     o.kennel_note =''
AND     o.country_short='de'
UNION ALL
SELECT   o.country_short,o.kennelname
FROM   owner o
INNER JOIN puppy p
ON     o.id = p.breeder_id
AND     p.active = '1'
ORDER BY o.country_short,o.kennelname

     ) t
ORDER BY t.country_short,t.kennelname
Alle diese Wege liefern das Selbe. Es ist einfach wichtig zu wissen wo die Reise hingeht. Du hast zunächst mal alle möglichen Spalten aller Tabellen mit einem falschen GROUP BY geholt, brauchen tust du die vermutlich nicht alle.
 
Also, Variante A und C liefern die gewünschte Ergebnismenge; Variante B liefert mir reichlich zuviele doppelte. C ist dabei mit Abstand das schnellste (0.0786 Sekunden).

Was aber leider völlig fehlt, ist die Möglichkeit das Jahr des letzten Wurfes zu berechnen sowie einen Bezug zu aktuellen Welpen (das war im ganz obigen Beispiel das date add und date sub).

Die aktuelle Ausgabe aus Variante C wäre jetzt z.B. id, kennelname aber eben nicht Jahr des letzten Wurfes sowie wer erwartet einen Wurf (obwohl im Select drin, liefert aber so nicht das gewünschte)....

Das ganze ist im live Betrieb unter aktive Züchter zu sehen - dort sind aber momentan nur die Züchter, die eine zuchtfähige Hündin haben (also die einen Wurf hatten und keine Hündin besitzen fehlen).

Wichtig sind mehr die aktuellen Welpen, das Jahr des letzten Wurfes könnte ich ggf. in einer gesonderten Spalte in der owner reinbringen....
 
Nach gefühlten 1000 Versuchen (alle möglichen Arten von Join kombiniert, mehrere Abfragen in arrays, Zusatzspalten in den Tabellen (was aber zeitgleich zweimal das gleiche in zwei Tabellen gewesen wäre), indexe angelegt) eine für mich brauchbare Lösung gefunden:
SELECT

owner.id,
owner.kennelname, owner.country_short, owner.kennel_note,
dog.id AS dogid, dog.breeder_id, dog.date_of_birth, dog.gender, dog.owner_id,
MAX(YEAR(dog.date_of_birth)) AS lastlitter,
puppy.breeder_id,

MAX(puppy.sollgeboren) AS birthday,
DATE_ADD(MAX(puppy.sollgeboren), INTERVAL 84 DAY) AS database_dateadd,
DATE_SUB(MAX(puppy.sollgeboren), INTERVAL 60 DAY) AS database_datesub,
puppy.active AS showing
FROM owner
INNER JOIN dog ON dog.owner_id = owner.id
LEFT JOIN puppy ON (dog.owner_id = puppy.breeder_id AND puppy.active='1')


WHERE
((
dog.gender='female'
AND dog.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
AND dog.breeding_approval_since !='0000'
)

OR
(
dog.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
))

AND owner.kennelname !=''
AND owner.kennel_note =''
AND owner.country_short='de'

GROUP BY owner.country_short, owner.kennelname

ORDER BY owner.kennelname ASC
in 0.0729 Sekunden
 
Werbung:
Es gibt viele Wege die zum Ziel führen, ich wollte einige mal demonstrieren (das mit den Dubletten bei Variante B sollte eigentlich durch DISTINCT entfallen). Wichtig ist es das Ziel zu kennen, also welche Informationen du brauchst und das scheinst du ja erkannt und umgesetzt zu haben.

Allerdings ist dein GROUP BY immernoch falsch, alle Spalten im Select-Teil müssen aggregiert oder gruppiert werden, der korrekte GROUP BY wäre also:
Code: 
GROUP BY
owner.id,owner.kennelname, owner.country_short, owner.kennel_note,
dog.id, dog.breeder_id, dog.date_of_birth, dog.gender, dog.owner_id,
puppy.breeder_id,puppy.active AS showing
Sonst wird es nach einem SQL Update nicht mehr laufen.
 
Zurück
Oben