nicht angemeldet
Query Laufzeit verbessern
MudGuardHi,
ich fürchte ich sehe hier den Wald vor lauter Bäumen nicht (bzw. bräuchte eine Bestätigung, dass ich nichts gravierendes übersehen habe).
Folgendes Problem: Ich habe zwei Tabellen (A und B) mit einer größeren Anzahl von Einträgen (A ~ 20.000 und B ~ 100.000). Den Einträgen in Tabelle A sind jeweils (über die EintragsID) einer oder mehrere Einträge aus B zugeordnet. Einzelne Zellen in den Tabellen können den Wert NULL haben.
Nun möchte ich gerne, geknüpft an einige Bedingungen, eine Trefferanzahl für jede Spalte auszählen lassen (also wieviele Wert ungleich NULL gibt es). Meine Ansatz war einfach die beiden Tabellen per LEFT JOIN zu verbinden, per WHERE die gewünschten Ergebnisse rauszufiltern und ein COUNT() über die einzelnen Spalten zu machen.
Das Problem hierbei ist, dass ein JOIN über diese beiden Tabellen schon ausgesprochen lange dauert. Nun frage ich mich, ob ich auf effektive Weise um diesen JOIN herum komme.
Um es etwas zu konkretisieren:
Tabelle A enthält Personendaten (sagen wir ID, Name, Gehalt, Zimmer) und in Tabelle B sind diesen Personen verschiedene Aufgaben (mit eintsprechenden Zusatzdaten) zugeordnet (also für unser Beispiel PersonenID, Name, Status, Deadline). Abgesehen von ID undn PersonenID können alle diese Felder auch den wert "NULL" haben. Nun möchte ich gerne auszählen wieviele Einträge ungleich NULL es für jede einzelne Spalte der Tabelle B gibt. Und das nur für Datensätze bei denen Gehalt größer als 1000 ist und der Status mindestens den Wert 2 hat. Derzeit mit JOIN sieht es etwa so aus
SELECT COUNT(*) as cGesamt, COUNT(b.Name) as cName, ... FROM A as a RIGHT JOIN B as b ON b.PersonenID = a.ID WHERE a.gehalt > 1000 AND b.Status >= 2
Wie gesagt, der JOIN braucht deutlich zu lange. Mir fällt aber auch keine bessere Möglichkeit ein (außer vielleicht im ersten Schritt alle zutreffenden IDs aus A abzuholen und diese dann beim auszählen mit in die WHERE Bedingung zu packen. Da riskiere ich aber im ungünstigsten Fall eine WHERE-Bedingung mit 20.000 Einträgen zu erstellen).
Irgend eine Idee wie sich das Problem lösen lässt? Datenbank ist MySQL 5.0
Grüße
-
Hallo Marc,
Folgendes Problem: Ich habe zwei Tabellen (A und B) mit einer größeren Anzahl von Einträgen (A ~ 20.000 und B ~ 100.000).
unproblematisch
Nun möchte ich gerne, geknüpft an einige Bedingungen, eine Trefferanzahl für jede Spalte auszählen lassen (also wieviele Wert ungleich NULL gibt es).
Das verstehe ich nicht. Bitte konkret anhand von Beispieldaten erläutern, welches Ergebnis Du haben möchtest - und warum das Ergebnis so aus den Ausgangsdaten folgt.
Das Problem hierbei ist, dass ein JOIN über diese beiden Tabellen schon ausgesprochen lange dauert. Nun frage ich mich, ob ich auf effektive Weise um diesen JOIN herum komme.
Nutze EXPLAIN, um zu ermitteln, warum dieser einfache Join zweier nicht allzu umfangreicher Tabellen lange dauert.
Um es etwas zu konkretisieren:
das ist alles andere als konkret.
Den Einträgen in Tabelle A sind jeweils (über die EintragsID) einer oder mehrere Einträge aus B zugeordnet.
SELECT COUNT(*) as cGesamt, COUNT(b.Name) as cName, ... FROM A as a RIGHT JOIN B as b ON b.PersonenID = a.ID WHERE a.gehalt > 1000 AND b.Status >= 2
Deine Query passt nicht zu Deiner Beschreibung.
Gibt es Personen, denen _keine_ Einträge in B zugeordnet sind.
Wenn nein, verwende einen INNER JOIN.
Wenn ja, interessieren Dich diese Personen.
Wenn nein, verwende einen INNER JOIN
Wenn ja, verwende einen LEFT und keinen RIGHT JOIN.Korrigiere Dein Statement, danach nutze EXPLAIN. Es könnte ein Problem mit Indizes vorliegen.
Freundliche Grüße
Vinzenz
-
Hallo !
Tabelle A enthält Personendaten (sagen wir ID, Name, Gehalt, Zimmer) und in Tabelle B sind diesen Personen verschiedene Aufgaben (mit eintsprechenden Zusatzdaten) zugeordnet (also für unser Beispiel PersonenID, Name, Status, Deadline). Abgesehen von ID undn PersonenID
Was war jetzt die EintragsID von der Du anfangs sprachst ?
Ich seh in B einen Fremdschluessel PersonenID der wohl auf A(ID) verweist.
Uebrigens - 'ID' ist nicht wirklich gut 'id' ist besser damit's zwischen den SQL-Ausdruecken in Grosschrift noch auffaellt.können alle diese Felder auch den wert "NULL" haben.
Dann ist der Fremschluessel aus B also NOT NULL ?
SELECT COUNT(*)
Muss das sein ? COUNT(a.ID) muesste doch auch reichen, oder ?
as cGesamt, COUNT(b.Name) as cName,
...Die '...' wuerde ich gern mal sehen
RIGHT JOIN B as b ON b.PersonenID = a.ID WHERE a.gehalt > 1000 AND b.Status >= 2
Wieso RIGHT [OUTER] JOIN ?
So ziehst Du alle B, egal ob sie A referenzieren oder nicht.
Da der FK in B aber NOT NULL ist, ergibt das einen INNER JOIN.Wie gesagt, der JOIN braucht deutlich zu lange.
Wie stelltst Du das fest ? Koennte auch an der WHERE clause liegen.
Mach mal ein EXPLAIN und poste das bitte.Sind das MyISAM oder InnoDB Tabellen ?
Gruesse
Holger
--
Aus dem Perl Styleguide:
"Choose mnemonic identifiers. If you can't remember what mnemonic means, you've got a problem."-
Hi,
Was war jetzt die EintragsID von der Du anfangs sprachst ?
Ich seh in B einen Fremdschluessel PersonenID der wohl auf A(ID) verweist.PersonenID verweist auf ID. Es gibt in der Tabelle B aber auch noch eine eindeutige ID, die ich vorher nicht erwähnt habe.
Die '...' wuerde ich gern mal sehen
Die ... sind weitere counts über die jeweils anderen Spalten. Also COUNT(b.gehalt) usw. Plus noch einige weitere Spalten, die ich der Übersichtlichkeit halber weggelassen habe.
Wieso RIGHT [OUTER] JOIN ?
Tippfehler. Ist ein LEFT JOIN.
Wie stelltst Du das fest ? Koennte auch an der WHERE clause liegen.
Da die WHERE-Bedingung ausgesprochen simpel ist halte ich das für eher unwahrscheinlich. Komplizierter als SpalteX > XYZ wird das nicht.
Mach mal ein EXPLAIN und poste das bitte.
[id] => 1
[select_type] => SIMPLE
[table] => a
[type] => ALL
[possible_keys] =>
[key] =>
[key_len] => [
ref] =>
[rows] => 20001
[Extra] =>[id] => 1
[select_type] => SIMPLE
[table] => b
[type] => ref
[possible_keys] => PersonenID
[key] => PersonenID
[key_len] => 8
[ref] => testdb.a.ID
[rows] => 5
[Extra] =>Tabellentype ist MyISAM.
Grüße
Marc
-
Hallo !
Hi,
Was war jetzt die EintragsID von der Du anfangs sprachst ?
Ich seh in B einen Fremdschluessel PersonenID der wohl auf A(ID) verweist.PersonenID verweist auf ID. Es gibt in der Tabelle B aber auch noch eine eindeutige ID, die ich vorher nicht erwähnt habe.
Die '...' wuerde ich gern mal sehen
Die ... sind weitere counts über die jeweils anderen Spalten. Also COUNT(b.gehalt) usw. Plus noch einige weitere Spalten, die ich der Übersichtlichkeit halber weggelassen habe.
Wieso RIGHT [OUTER] JOIN ?
Tippfehler. Ist ein LEFT JOIN.
:-| Das ist schon ein KLEINER Unterschied, oder ?
Bevor ich hier poste mach ich kurze Tests.
Aber ungern nur zum Spielen.Wie stelltst Du das fest ? Koennte auch an der WHERE clause liegen.
Da die WHERE-Bedingung ausgesprochen simpel ist halte ich das für eher unwahrscheinlich. Komplizierter als SpalteX > XYZ wird das nicht.
Mach mal ein EXPLAIN und poste das bitte.
[id] => 1
[select_type] => SIMPLE
[table] => a
[type] => ALL
[possible_keys] =>
[key] =>
[key_len] => [
ref] =>
[rows] => 20001
[Extra] =>[id] => 1
[select_type] => SIMPLE
[table] => b
[type] => ref
[possible_keys] => PersonenID
[key] => PersonenID
[key_len] => 8
[ref] => testdb.a.ID
[rows] => 5
[Extra] =>=> Wie waer's denn mit einem PRIMARY KEY auf A(ID) ? <=
;-)
Ohne Indices schneckt das auch bei mir ( 32k A / 64 k B Saetze )Tabellentype ist MyISAM.
Beim JOIN ueber den PK soll InnoDB schneller sein.
Ist Dir klar dass Du mit MyISAM KEINE echten referentiellen INtegritaeten hast ?Wenn Du aus A einen in B referenzierten Satz loescht wuerde trotz ON DELETE / ON UPDATE clause nichts passieren - ausser dass die ref. Int. nicht mehr gegeben waere !
Das ist KEIN Scherz !
Mit InnoDB kann man das alles vermeiden.
Grüße
Marc
Gruesse
Holger
--
Aus dem Perl Styleguide:
"Choose mnemonic identifiers. If you can't remember what mnemonic means, you've got a problem."-
Hi,
=> Wie waer's denn mit einem PRIMARY KEY auf A(ID) ? <=
;-)Ist bereits. Primary auf A.ID und B.ID und INDEX auf B.PersonenID
Beim JOIN ueber den PK soll InnoDB schneller sein.
Werde es ausprobieren.
Marc
-
Hi,
=> Wie waer's denn mit einem PRIMARY KEY auf A(ID) ? <=
;-)Ist bereits. Primary auf A.ID und B.ID und INDEX auf B.PersonenID
Nein, ist es nicht.
Das zeigt EXPLAIN doch ganz eindeutig.Und dann und nur dann wenn ein Index fehlt, scheckt es.
Beim JOIN ueber den PK soll InnoDB schneller sein.
Werde es ausprobieren.
Marc
--
Aus dem Perl Styleguide:
"Choose mnemonic identifiers. If you can't remember what mnemonic means, you've got a problem."
-
-
-
-
-
Hi,
Folgendes Problem: Ich habe zwei Tabellen (A und B) mit einer größeren Anzahl von Einträgen (A ~ 20.000 und B ~ 100.000). Den Einträgen in Tabelle A sind jeweils (über die EintragsID) einer oder mehrere Einträge aus B zugeordnet. Einzelne Zellen in den Tabellen können den Wert NULL haben.
Nun möchte ich gerne, geknüpft an einige Bedingungen, eine Trefferanzahl für jede Spalte auszählen lassen (also wieviele Wert ungleich NULL gibt es). Meine Ansatz war einfach die beiden Tabellen per LEFT JOIN zu verbinden, per WHERE die gewünschten Ergebnisse rauszufiltern und ein COUNT() über die einzelnen Spalten zu machen.
Das Problem hierbei ist, dass ein JOIN über diese beiden Tabellen schon ausgesprochen lange dauert. Nun frage ich mich, ob ich auf effektive Weise um diesen JOIN herum komme.Welche Indizes hast Du in den Tabellen angelegt?
cu,
Andreas--
Warum nennt sich Andreas hier MudGuard?
Schreinerei Waechter
O o ostern ...
Fachfragen unaufgefordert per E-Mail halte ich für unverschämt und werde entsprechende E-Mails nicht beantworten. Für Fachfragen ist das Forum da.
-
Hi,
Tabelle A einen Primary Key auf die ID und ein INDEX auf das Gehalt, Tabelle B ein PRIMARY KEY ebenfalls auf die ID und einen INDEX auf PersonenID.
Grüße
-