langsame MySQL Abfrage

[tobi85]

Hallo,

ich habe ein Problem mit einer MYSQL Abfrage, weil diese sehr langsam ist. Mir ist bewusst, dass man hier wenig dazu sagen kann, wenn man die DB-Struktur nicht kennt, aber ggf. kann mir ja doch jemand helfen, wie ich die Anfrage effektiver gestalten kann, bzw. wo ich noch indexes setzen sollte.

Die Abfrage soll beim einem Artikel schauen, welche Artikel andere Kunden auch noch gekauft hatten.

SELECT COUNT(artikel_attribut.anr) as count, artikel_attribut.anr 
FROM artikel_attribut
JOIN artikel ON artikel.anr=artikel_attribut.anr AND artikel.anr!='1'
JOIN auftrag_artikel ON auftrag_artikel.atrnr=artikel_attribut.atrnr AND auftrag_artikel.date>'2016-01-04' 

WHERE  auftrag_artikel.auftragnr IN (SELECT auftrag_artikel.auftragnr FROM auftrag_artikel JOIN artikel_attribut ON artikel_attribut.atrnr=auftrag_artikel.atrnr AND artikel_attribut.anr='1')

GROUP BY artikel_attribut.anr
ORDER BY count DESC
LIMIT 6

Aktuell braucht diese Abfrage über eine halbe Sekunde

id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra 1|PRIMARY|artikel|range|PRIMARY|PRIMARY|4||2463|Using where; Using index; Using temporary; Using filesort 1|PRIMARY|artikel_attribut|ref|PRIMARY, INDEX_ANR, INDEX_PREIS2, INDEX_ARTIKEL_PREIS|INDEX_ANR|4|slewo_live.artikel.anr|7|Using where; Using index 1|PRIMARY|auftrag_artikel|ref|INDEX_ATRNR|INDEX_ATRNR|4|slewo_live.artikel_attribut.atrnr|3|Using where 2|DEPENDENT SUBQUERY|auftrag_artikel|ref|INDEX, INDEX_ATRNR|INDEX|4|func|1| 2|DEPENDENT SUBQUERY|artikel_attribut|eq_ref|PRIMARY, INDEX_ANR, INDEX_PREIS2, INDEX_ARTIKEL_PREIS|PRIMARY|4|slewo_live.auftrag_artikel.atrnr|1|Using where

[dedlfix]

Tach!

ich habe ein Problem mit einer MYSQL Abfrage, weil diese sehr langsam ist. Mir ist bewusst, dass man hier wenig dazu sagen kann, wenn man die DB-Struktur nicht kennt, aber ggf. kann mir ja doch jemand helfen, wie ich die Anfrage effektiver gestalten kann, bzw. wo ich noch indexes setzen sollte.

Effektiver geht nicht, wenn sie bereits das gewünschte Ergebnis liefert. Du willst stattdessen die Effizienz erhöhen. Und das ist so eine Sache, ohne dass man die originalen Daten zur Verfügung hat. Die Struktur allein hilft nicht, weil der Ausführungsplan sich auch je nach Menge der Daten ändern kann. Es lohnt nicht, für drei Datensätze einen Indexe zu verwenden. (Deswegen ist auch bei einigen Einträgen der EXPLAIN-Ausgabe auch kein "Using index", obwohl possible_keys einen Eintrag hat.

Versuch mal die Joins rauszunehmen, wenn du gar keine Daten von den gejointen Tabellen brauchst (also alle). Nimm stattdessen Subquerys in der WHERE-Klausel, beispielsweise mit EXISTS.

dedlfix.

[tobi85]

Hallo und danke!

Also die JOINs brauche ich, denn das Skript muss im JOIN erstmal die Artikelnummer (Gruppe) finden, denn im Auftrag stehen nur die Einzel-Artikel (Hose Rot - XL) und ich möchte ja auf Artikelgruppe (im Beispiel Hose Rot) herausfinden, welche Artikel andere Kunden noch gekauft hatten. (Beispiel Hose Grün)

Weöchen Vorteile habe ich denn mit Subquerys. Sind diese so viel schneller?

[dedlfix]

Tach!

Also die JOINs brauche ich, denn das Skript muss im JOIN erstmal die Artikelnummer (Gruppe) finden, denn im Auftrag stehen nur die Einzel-Artikel (Hose Rot - XL) und ich möchte ja auf Artikelgruppe (im Beispiel Hose Rot) herausfinden, welche Artikel andere Kunden noch gekauft hatten. (Beispiel Hose Grün)

Weöchen Vorteile habe ich denn mit Subquerys. Sind diese so viel schneller?

Zur Geschwindigkeit habe ich noch keine Versuche angestellt. Vielleicht bringt das was, weil nicht erst eine große Datenmenge erstellt werden muss, in der dann gesucht wird, sondern weil die entsprechenden Bedingungen in kleinen Datenmengen direkt in den Tabellen und deren Indizes überprüft werden können. Vielleicht liege ich mit der Vermutung aber auch komplett daneben.

Die Subquerys sind jedoch aus meiner Sicht expressiver. Sie sagen deutlicher und geradeheraus was man will und verstecken das nicht in einem Join.

Ich nehme nur mal den ersten Teil der Query und formuliere den um

SELECT COUNT(artikel_attribut.anr) as count, artikel_attribut.anr 
FROM artikel_attribut
JOIN artikel ON artikel.anr=artikel_attribut.anr AND artikel.anr!='1'

SELECT COUNT(anr) as count, anr 
FROM artikel_attribut
WHERE EXISTS (SELECT * FROM artikel WHERE anr = artikel_attribut.anr AND anr != '1')

Zudem bekommst du durch die große Query durch die Subquerys in kleinere Teile zerlegt, die sich auch einzeln testen lassen. So kannst du schauen, ob sie die Teile an sich ein richtiges Ergebnis liefern. Einen Mehrfachjoin kannst du nur debuggen, indem du Joins entfernst und wieder hinzufügst und schaust, welchen Einfluss das auf die restliche Query hat.

Vielleicht wird durch die Subquerys das ganze Konstrukt durchschaubarer und du findest Teile, die du weglassen oder vereinfachen kannst. In deiner Query verwendest du beipielsweise die Tabelle auftrag_artikel zweimal.

dedlfix.

[tobi85]

SELECT COUNT(anr) as count, anr 
FROM artikel_attribut
WHERE EXISTS (SELECT * FROM artikel WHERE anr = artikel_attribut.anr AND anr != '1')

Sorry - aber ich bekomme das nicht hin. EXISTS funktioniert bei einer Tabelle aber ich muss ja über 4 Tabellen verknüpfen.

[dedlfix]

Tach!

Sorry - aber ich bekomme das nicht hin. EXISTS funktioniert bei einer Tabelle aber ich muss ja über 4 Tabellen verknüpfen.

Die ersten beiden Joins verbinden sich mit artikel_attribut. Jeder Join schränkt unabhängig vom anderen die Menge der Daten von artikel_attribut ein. Also sind es zwei separate EXISTS-Bedingungen, die mit AND verknüpft werden müssen (und dazu noch der Rest aus der schon bestehenden WHERE-Klausel).

dedlfix.

[pl]

Dein explain sieht auf den ersten Blick gut aus von daher vermute ich die Bremse bei WHERE IN(...) also in einer womöglich zu langen Liste die WHERE durchackern muss. Versuch macht schlau, teste die Abfragen mal einzeln, so würde ich da rangehen.

[tobi85]

Hallo, welche ALternative gibt es denn in IN.

In der SUBQUERY sucht er ale Auftragnummer, bei welchem der Artikel X gekauft wurde. Das können unter Umständen schon mehr als 100 Nummern sein. Nun hat er AUftragnummern mit den Artikeln und sucht in den Aufträgen nach anderen Artikeln, die gekauft wurden. Wenn das abgeschlossen wurde, soll er diese Artikelnummern noch gruppieren, zählen und nach Häufigkeit sortieren.

[Rolf b]

Hallo tobi85,

für das, was Du vorhast, wird der IN schon nötig sein. Der Explain deutet aber an, dass er erstmal den äußeren Select durchführt, was zu einem fetten kartesischen Produkt führt, und es dann auf die im inneren Select gefundenen Artikelnummern reduziert.

Dieses kartesische Produkt musst Du abspecken. Brauchst Du überhaupt den JOIN zur Artikeltabelle? Wenn ich das richtig sehe, wird daraus gar nichts verwendet; die anr steht auch in artikel_attribut.

Dein Subselect sucht in (auftrag_artikel JOIN artikel_attribut) alle Auftragsnummern, in denen ein Artikel mit anr=1 enthalten ist. Der äußere Select macht diesen Join im Prinzip nochmal, sucht darin für die Auftragsnummern alle anr ungleich 1 und zählt, welche anr wie oft vorkommt. Der JOIN mit Artikel erreicht die Zusicherung, dass es zu dieser ANR einen Artikel gibt. Brauchst Du diese Zusicherung?

Den Subselect kannst Du theoretisch auch auf die Ebene des äußeren Select heben; aber ob die Query dadurch schneller wird kann ich nicht sagen. Man kann dadurch aber eine "natürlichere" Reihenfolge angeben:

• Bilde (auftrag_artikel JOIN artikel_attribut) und reduziere auf ANR='1'
• Joine alle Aufträge zu diesen Auftragsnummer hinzu
• Joine alle artikel_attribut mit ANR!='1' hinzu

SELECT COUNT(t2.anr) as count, t2.anr 
FROM artikel_attribut t1 
JOIN auftrag_artikel a1 ON a1.atrnr=t1.atrnr AND t1.anr = '1'
JOIN auftrag_artikel a2 ON a1.auftragnr = a2.auftragnr
                       AND a2.date>'2016-01-04' 
JOIN artikel_attribut t2 ON a2.atrnr = t2.atrnr AND t2.anr != '1'
-- ?? JOIN artikel ON artikel.anr=t2.anr 

GROUP BY t2.anr
ORDER BY count DESC
LIMIT 6

Gruß Rolf

[tobi85]

Hallo und danke. Wird so leider nicht funktionieren, aber das liegt daran, dass Du die Tabellenstruktur nicht kennst. Ich versuche diese mal kurz zu skizieren

In meinem Beispiel möchte ich also wissen, was Kunden noch bestellt hatten, die Artikel Hose (ANR 1) bestellt hatten. In meinen Beispiel kommt als Ergebnis ANR 2 und 3. In der Tabelle Auftrag Artikel habe ich keine Artikelnummer (Gruppennummer) stehen, sondern nur die Attributnummer.

Bitte entschuldigt, aber ich habe keine Ahnung, wie ich hier eine Tabelle erstelle.

Tabelle Artikel

ANR | Name | Available 1 | Hose | 1 2 | Jeans| 1 3 | Pulli| 1

Tabelle Artikel_attribut

ATRNR | ANR | NAME 99 | 1 | Größe L 100 | 1 | Größe XL 101 | 2 | 42 102 | 2 | 44 103 | 3 | Rot L

Tabelle Auftrag_Artikel

AUFTRAGNUMMER | ATRNR 1 | 99 1 | 101 1 | 103

[Matthias Apsel]

Hallo tobi85,

Bitte entschuldigt, aber ich habe keine Ahnung, wie ich hier eine Tabelle erstelle.

„Dieses Forum nutzt Markdown. Im Wiki erhalten Sie Hilfe bei der Formatierung Ihrer Beiträge.“

Bis demnächst
Matthias

[Rolf b]

Hallo tobi85,

doch, ich denke mein SQL passt schon. Und weil die anr in der Artikel_Attribut Tabelle drinsteht, brauchst Du in keinem Fall einen Join zur Artikeltabelle. Zur "Wo ist das noch" Suche reicht ja die ANR. Die Artikeltabelle brauchst Du erst im zweiten Schritt, wenn du die beliebtesten "Auch-Käufe" gefunden hast.

Man kann es sicher noch anders formulieren, mit anderer Anordnung von IN oder einem EXIST, aber ein ordentlicher SQL Optimizer sollte immer was sinnvolles draus machen. Beispielsweise ginge auch das hier, das habe ich gerade im SQL Server gebastelt.

SELECT COUNT(t2.anr) as Anzahl, anr
FROM Artikel_attribut t2 
     JOIN Auftrag_artikel a2 ON a2.atrnr = t2.ATRNR
WHERE t2.anr != '1'
AND a2.auftragnummer IN (SELECT a1.auftragnummer
                         FROM auftrag_artikel a1
                              JOIN artikel_attribut t1 ON a1.atrnr = t1.atrnr
                         WHERE a1.date > '2016-04-01' AND t1.anr = '1')
GROUP BY t2.anr
ORDER BY COUNT(t2.anr) desc

Der Select im IN ermittelt alle Auftragsnummern seit 01.04.16, wo Hosen dabei waren (ANR=1), der äußere Select sucht zu diesen Aufträgen alle Artikel, die keine Hosen sind. Und die werden dann gruppiert.

ABER ACHTUNG. Wenn dein Shop erfolgreich ist, wirst Du aber auch bei dem besten SQL irgendwann ein Performance-Problem bekommen. Die korrekte Lösung heißt: Prepared Data. Das ist eine Tabelle, die Du nicht live pflegst, sondern in einem Batchjob, z.B. morgens um 3. Die einfachste Prepared Data Lösung würde eine Tabelle sein, die Auftragsnummer, Auftragsdatum und ANR kombiniert, damit Du den Join Auftrag_Artikel mit Artikel_Attribut nicht live machen musst. Wenn auch das zu langsam ist, brauchst Du eine Tabelle mit Spalten "ANR_gekauft", "ANR_auchgekauft", "Anzahl_Aufträge", "Kaufdatum", wo Du dann einfach mit der Hose einsteigst und dann die auch gekauften ANR mit der Anzahl der Aufträge, in denen das der Fall ist, herausbekommst ohne irgendwas zu Joinen. Das läuft dann irgendwann nachts eine Stunde lang und bei der Gelegenheit löschst Du dann auch gleich alles aus der Referenztabelle heraus, was zu alt ist (in deinem Beispiel: Älter als 04.01.2016), damit keine unnötigen Daten durchsucht werden. Für diesen Batch-SQL kannst Du dann aber kein WHERE...IN verwenden, der muss meinem ersten Versuch mit dem 4-fach Join ähneln (Wie genau? Das ist eine Übung für den Leser :-> ). Damit bist Du dann tagsüber flott unterwegs. Zwar nur mit den Daten bis gestern, aber das ist bei dieser Aufgabe wohl weniger relevant.

Gruß Rolf