MySQL Query Optimierung

2

27o8

abgemeldet
2 Mai 2006
9.028
933
Code: 
# Time: 090925 17:39:50
# User@Host: web10[web10] @ localhost []
# Query_time: 25  Lock_time: 0  Rows_sent: 34  Rows_examined: 5904964
SELECT werber, COUNT(*) AS menge
FROM mitglieder
WHERE id in
( SELECT uid
FROM teilnahmen 
WHERE status = 1 AND zeit BETWEEN 1249034400 AND 1262257200
GROUP BY uid
HAVING COUNT(*) >=3) AND werber != 0
GROUP BY werber ORDER BY menge DESC;
Der Query braucht ganze 25 Sekunden, wo müsste ich sinvolle Indexe setzen um das ganze zu beschleunigen?

Aktuell sind (unter anderem) folgende drin:
PRIMARY mitglieder.id
INDEX mitglieder.werber
INDEX teilnahmen.zeit teilnahmen.status

Rows_examined ist ziemlich hoch, aber mir fehlen die Ideen ;)
 
Bei einigen Datenbanksystem wird ein SELECT ... FROM ... WHERE xyz IN (SELECT ... FROM ...) umgesetzt in SELECT ... FROM ... WHERE xyz = [Ergebnis 1 aus Sub-SELECT] OR xyz = [Ergebnis 2 aus Sub-SELECT] OR ...

...ich hoffe du weißt worauf ich hinaus will. Bei großen Ergebnismengen des Sub-Selectes kann das ein Flaschenhals sein. Ich würde zunächst versuchen den Sub-Select aufzulösen, z.B. mit einer temporären Tabelle, die Du dann per INNER JOIN mit der führenden Tabelle verknüpfst.
 
Jup, ich würd versuchen die Subquery aufzulösen.
Der Primary-Key wird afair nicht akzeptiert, weil du ein Group-By in deiner Query hast. Dafür gilt der Primary-Key nämlich nicht, weil das eine ganz andere Menge ist, die durchsucht wird.
Und ich kann Bestätigen, dass Subqueries in MySQL sehr langsam sein können. Hatte vor kurzem ne Query, die brauchte mit Subqueries ne halbe Minute. Nach dem Einsatz von Joins bin ich auf unter eine Sekunde gekommen. (Tabelle mit ~350.000 Einträgen und teilweise 5 Joins/Subqueries)
 
Was brauchst du genau? das wichtigste steht oben in Posting 1, die anderen Felder sind nicht von Bedeutung :)
 
Seemann schrieb:
...ich hoffe du weißt worauf ich hinaus will. Bei großen Ergebnismengen des Sub-Selectes kann das ein Flaschenhals sein. Ich würde zunächst versuchen den Sub-Select aufzulösen, z.B. mit einer temporären Tabelle, die Du dann per INNER JOIN mit der führenden Tabelle verknüpfst.
Zum Vergrößern anklicken....
in MySQL wird sowas dann zu einem Join umgeandelt, dependent subquerys, wie hier, werden das sowieso.

flaschenkind schrieb:
Und ich kann Bestätigen, dass Subqueries in MySQL sehr langsam sein können. Hatte vor kurzem ne Query, die brauchte mit Subqueries ne halbe Minute. Nach dem Einsatz von Joins bin ich auf unter eine Sekunde gekommen. (Tabelle mit ~350.000 Einträgen und teilweise 5 Joins/Subqueries)
Zum Vergrößern anklicken....
Subquerys sind in MySQL noch miserabel optimiert, erst für MySQL soll es da viele neue Optimierungen geben, oder wenn sie auf eine 5er Version zurückportiert werden.


Hast du noch ein paar Angaben, was du eigentlich bewirken willst? Ist es auch möglich das Datenbankschema für Performancezwecke zu verändern?
Ich nehme auch mal an, dass die uid in dem Subquery für die Dependency zuständig ist?
 
Hast du noch ein paar Angaben, was du eigentlich bewirken willst?
Zum Vergrößern anklicken....
Natürlich, also ich habe eine Tabelle teilnahmen. Wenn ein User an einer bestimmten Aktion teilnimmt wird das in dieser Tabelle eingetragen, und zwar mit uid, zeit wenn die Teilnahme überprüft wird wird der Status auf 1 gesetzt.

Jeder User hat auch einen Werber (kennst du bestimmt von Klamm etc.), dieser steht halt in der mitglieder Tabelle im Feld werber.

Bei mir auf der Seite gibt es nun einen Wettbewerb bei dem es darum geht neue Mitglieder zu werben. Damit man aber nur für aktive Mitglieder einen Punkt bekommt sollen nur alle Mitglieder zählen die zwischen Start und Ende des Wettbewerbes geworben wurden (das fehlt noch oben in dem Query, aber ist auch nicht wichtig da es der erste Wettbewerb ist und somit alle zählen), und während dem Wettbewerb (spalte zeit) an mindestens 3 Aktionen erfolgreich (spalte status=1) teilgenommen haben.

Ist es auch möglich das Datenbankschema für Performancezwecke zu verändern?
Zum Vergrößern anklicken....
Wenn es gar nicht anders geht wäre das möglich, wobei ich dann erst noch darüber nachdenken würde einen Cache einzubauen, sodass der aktuelle Stand vom Wettbewerb nur alle x Stunden aktualisiert würde.
 
Also der Query lässt sich so nimmer groß optimieren.

Was der Query bewirkt, hab ich doch dann richtig erraten, ich würde folgende Lösung vorschlagen, wie führen eine neue Tabelle ein, die eine Zusammenfassung ist.

Diese Tabelle bezieht sich auf solch einen Wettbewerb.
Darin ist pro Mitglied dann ein Eintrag wieviele Punkte (geworbene Mitglieder mit erfüllten Bedingungen hat).
Um dies mit möglichst wenig Aufwand verwalten zu können, werden die Daten automatisch per Trigger aus der Teilnahmen-Tabelle geupdated.
 
Ich hab da gerade noch ne idee gehabt, sollte ne ganze Ecke effizienter sein, aber noch nicht so gut wie die Trigger-Lösung:

Code: 
SELECT mitglieder.werber, COUNT(*) AS menge
FROM
  (SELECT uid
   FROM teilnahmen 
   WHERE status = 1 AND zeit BETWEEN 1249034400 AND 1262257200
   GROUP BY uid
   HAVING COUNT(*) >=3) as teilnahmen_cnt
JOIN mitglieder ON(mitglieder.id = teilnahmen_cnt.uid)
WHERE mitglieder.werber != 0
GROUP BY mitglieder.werber ORDER BY menge DESC;

damit wird man den sehr uneffizienten Dependent Subquery los.
 
War grad schon die Dokus über Trigger am studieren als ne Benachrichtigung über neue Antworten per Mail kam :biggrin:.

die Abfrage dauerte 0.0102 sek.
Zum Vergrößern anklicken....

Im Vergleich zu den 25 Sekunden vorher ist das eine enorme Steigerung. Hab zwar mit Triggern noch keine Erfahrung aber glaube nicht, dass es dadurch noch spürbar schneller würde.

DANKE auf jedenfall.
 
Trigger scheinen ganz interessant zu sein, werde mich damit auf jedenfall mal näher befassen. War ehrlich gesagt, das erste mal das ich davon gehört habe. Aber beim lesen der ersten Seiten der Doku wurde ich schon neugierig :)
 
Mit triggern koenntest du eine art cache tabelle bauen auf die du die query ausfuehrst, anstatt auf die life daten die extra aggregiert werden muessen.
 
Du musst dich einloggen oder registrieren, um hier zu antworten.
Zurück
Oben