nicht angemeldet
Join Abfrage unheimlich langsam
Hallo Ihr Lieben,
ich habe folgendes Problem, zwei Tabellen (siehe Create unten).
Diese Versuche ich mit einem LEFT JOIN zu verknüpfen.
Primary Key von auftrag ist auf_index und der foregin key von ba ist ebenfalls auf_index. Es gibt also immer ein auf_index in auftrag aber nicht immer ein auf_index in ba.
Folgender Join wird benutzt:
SELECT A.auf_index, A.auf_auflagentermin, B.ba_lfnr, B.ba_datum, B.ba_arbeitsvermerk, B.ba_auflagentermin, B.ba_auflagentermin_ok
FROM auftrag A LEFT JOIN ba B ON (A.auf_index=B.auf_index)
where
where (A.auf_typ='Auftrag' AND A.auf_auflagentermin is not NULL AND A.auf_auflagentermin_ok is NULL AND A.auf_auflagentermin>="2004-05-03" AND B.auf_index IS NULL) OR (A.auf_typ='BA' AND B.ba_auflagentermin is not NULL AND B.ba_auflagentermin_ok is NULL AND B.ba_auflagentermin>="2004-05-03" AND A.auf_index=B.auf_index) and ((A.auf_typ='Auftrag' and B.ba_auflagentermin_ok is NULL and B.ba_auflagentermin is not NULL or A.auf_typ='BA')) ORDER BY B.ba_auflagentermin desc, A.auf_index desc;
Wie kann man die Abfrage optimieren? ich will nur Datensätze wo ein auf_auflagentermin!=NULL und ba_auflagentermin!=NULL vorhanden ist.
Kann man die Verknüpfung vor der Where Clausel einschränken?
Tabelle auftrag hat ca 4000 Datensätze und ba ca. 1000.
Table structure for table 'auftrag'
CREATE TABLE auftrag (
auf_datum date default NULL,
auf_auftrag blob,
auf_bearb2 varchar(50) default NULL,
auf_index bigint(20) NOT NULL auto_increment,
auf_auftragsart varchar(20) NOT NULL default '',
auf_auflagentermin date default NULL,
auf_auflagentermin_ok tinyint(3) unsigned default NULL,
PRIMARY KEY (auf_index)
) TYPE=MyISAM;
Table structure for table 'ba'
CREATE TABLE ba (
ba_lfnr bigint(20) NOT NULL auto_increment,
ba_datum date default NULL,
ba_auflagentermin date default NULL,
ba_auflagentermin_ok tinyint(3) unsigned default NULL,
PRIMARY KEY (ba_lfnr),
UNIQUE KEY ba_lfnr (ba_lfnr)
) TYPE=MyISAM;
Vielen Dank für die Hilfe
RObert
-
Moin!
Ganz einfach: alle swas hinter WHERE, OR oder AND steht muss indiziert sein, dann gehts schnell.
MFFG (Mit freundlich- friedfertigem Grinsen)
fastix®
--
Als Freiberufler bin ich immer auf der Suche nach Aufträgen: Schulungen, Development. Auch für seriöse Agenturen.-
Moin!
Ganz einfach: alle swas hinter WHERE, OR oder AND steht muss indiziert sein, dann gehts schnell.
Wie meinst du das mit indiziert? Habe alle Spalten aus der Where als Index gesetzt aber kein Erfolg. Dauert immer noch zu lange die Abfrage. Ein normaler Join braucht ca 0,5 sec und meine Abfrage ca 5
Sec. Das ist zu lange.Noch andere Ideen? Kannst du mir das mit dem Inizieren genauer beschreiben? vielleicht habe ich da was falsch gemacht.
Gruß RObert
MFFG (Mit freundlich- friedfertigem Grinsen)
fastix®
-
Hallo,
Ganz einfach: alle swas hinter WHERE, OR oder AND steht muss indiziert sein, dann gehts schnell.
Wie meinst du das mit indiziert? Habe alle Spalten aus der Where als Index gesetzt aber kein Erfolg. Dauert immer noch zu lange die Abfrage. Ein normaler Join braucht ca 0,5 sec und meine Abfrage ca 5
Sec. Das ist zu lange.Was vergleichst Du hier?
Wenn Du die Anforderungen an Dein Resultset mit einem normalen Join erfüllen könntest, brauchtest Du keinen Outer-Join. Das musst Du natürlich zuerst klären. Also: Einen Outer-Join setzt man _nur_ ein, wenn es keine andere Möglichkeit gibt.
Wenn Du einen Outer-Join brauchst, muss Dir klar sein, dass dieser länger brauchen muss, als ein optimierter Standard-Join. Bei den vielen Nebenbedingungen, die Du da abprüfst, kann das schon mal 10 Mal solange sein.
Noch andere Ideen? Kannst du mir das mit dem Inizieren genauer beschreiben? vielleicht habe ich da was falsch gemacht.
http://www.mysql.de/doc/de/CREATE_INDEX.html
Du könntest natürlich auch nochmal über den Tabellenaufbau nachdenken. Helfen kann man Dir dabei allerdings nur, wenn Du mal den richtigen aktuellen Stand zeigst und näher beschreibst, welche Datensätze Du als Resultat herausziehen willst. Dein Create Table vom Ausgangsposting kann nämlich nicht zum SQL-Satement passen. Es gibt dort z.B. die Spalten B.auf_index und A.auf_typ gar nicht.
viele Grüße
Axel
-
Moin!
Es gibt dort z.B. die Spalten B.auf_index und A.auf_typ gar nicht.
Daran stimmt noch mehr nicht. Überflüssige Klammern etc. Ich habe Zweifel, dass das überhaupt funktioniert hat.
MFFG (Mit freundlich- friedfertigem Grinsen)
fastix®
--
Als Freiberufler bin ich immer auf der Suche nach Aufträgen: Schulungen, Development. Auch für seriöse Agenturen.-
Danke!
Leute Ihr seit Spitze, die Lösung war wirklich ein Index auf auf_index in der Tabelle ba. Alle anderen Felder habe ich indiziert aber auf_index nicht da es ein foreign Key war, dachte ich das Mysql ihn selbsständig indiziert. Aber weit gefehlt.
Wunderbar
DANKE
-
-
Danke!
Leute Ihr seit Spitze, die Lösung war wirklich ein Index auf auf_index in der Tabelle ba. Alle anderen Felder habe ich indiziert aber auf_index nicht da es ein foreign Key war, dachte ich das Mysql ihn selbsständig indiziert. Aber weit gefehlt.
Wunderbar
DANKE
-
-
-
-
Hallo,
Ich nehme mal an, das doppelte WHERE war im original SQL nicht vorhanden? ;-)).
SELECT A.auf_index, A.auf_auflagentermin, B.ba_lfnr, B.ba_datum, B.ba_arbeitsvermerk, B.ba_auflagentermin, B.ba_auflagentermin_ok
FROM auftrag A LEFT JOIN ba B ON (A.auf_index=B.auf_index)
where(A.auf_typ='Auftrag' AND A.auf_auflagentermin IS NOT NULL AND A.auf_auflagentermin_ok IS NULL AND A.auf_auflagentermin>="2004-05-03" AND B.auf_index IS NULL)
OR
(A.auf_typ='BA' AND B.ba_auflagentermin IS NOT NULL AND B.ba_auflagentermin_ok IS NULL AND B.ba_auflagentermin>="2004-05-03"
AND A.auf_index=B.auf_index)^Wenn das zutrifft, dann gilt: B.auf_index IS NOT NULL
AND
(A.auf_typ='Auftrag' AND B.ba_auflagentermin_ok IS NULL AND B.ba_auflagentermin IS NOT NULL OR A.auf_typ='BA')
=>
(A.auf_typ='Auftrag' AND A.auf_auflagentermin IS NOT NULL AND A.auf_auflagentermin_ok IS NULL AND A.auf_auflagentermin>="2004-05-03" AND B.auf_index IS NULL)
OR
(A.auf_typ='BA' AND B.ba_auflagentermin IS NOT NULL AND B.ba_auflagentermin_ok IS NULL AND B.ba_auflagentermin>="2004-05-03"
AND B.auf_index IS NOT NULL)
AND
(A.auf_typ='Auftrag' AND B.ba_auflagentermin_ok IS NULL AND B.ba_auflagentermin IS NOT NULL OR A.auf_typ='BA')Zuordnung:
A.auf_typ='Auftrag' = X0
A.auf_auflagentermin IS NOT NULL = X1
A.auf_auflagentermin_ok IS NULL = X2
A.auf_auflagentermin>="2004-05-03" = X3
B.auf_index IS NULL = X4; B.auf_index IS NOT NULL = NOT X4A.auf_typ='BA' = X5
B.ba_auflagentermin IS NOT NULL = X6;
B.ba_auflagentermin_ok IS NULL = X7;
B.ba_auflagentermin>="2004-05-03" = X8=>
(X0 AND X1 AND X2 AND X3 AND X4)
OR
(X5 AND X6 AND X7 AND X8 AND NOT X4)
AND
(X0 AND X7 AND X6 OR X5)http://www-ihs.theoinf.tu-ilmenau.de/~sane/projekte/karnaugh/embed_karnaugh.html
Leider können hier nur maximal 6 Variablen betrachtet werden. Wir betrachten deshalb nur:
(X5 AND X6 AND X7 AND X8 AND NOT X4)
AND
(X0 AND X7 AND X6 OR X5)
und erhalten:
(NOT X4 AND X8 AND X7 AND X6 AND X5)=>
where (X0 AND X1 AND X2 AND X3 AND X4) OR
(NOT X4 AND X8 AND X7 AND X6 AND X5)=>
where (A.auf_typ='Auftrag' AND A.auf_auflagentermin IS NOT NULL AND A.auf_auflagentermin_ok IS NULL AND A.auf_auflagentermin>="2004-05-03" AND B.auf_index IS NULL) OR
(B.auf_index IS NOT NULL AND B.ba_auflagentermin>="2004-05-03" AND B.ba_auflagentermin_ok IS NULL AND B.ba_auflagentermin IS NOT NULL AND A.auf_typ='BA')Angaben ohne Gewähr ;-))
Die Vorgehensweise sollte aber klar sein. Wenn Du Lust hast, kannst Du auch einen eigenen Karnaugh-Plan aufstellen, der dann alle Variablen abdeckt. Zur Theorie des Minimierungsverfahrens nach Karnaugh habe ich auf die Schnelle nichts wirklich umfassendes gefunden. Hier http://maui.theoinf.tu-ilmenau.de/~sane/skript_new/node27.html#SECTION00042100000000000000 wird es kurz und _sehr_ mathematisch beschrieben. Eventuell hat jemand einen besseren Link. Der würde mich auch interessieren.
viele Grüße
Axel
-
Hallo Axel,
die Abfragegeschwindigkeit bleibt gleich. Habe die where Clausel so angepaßt wie du es vorgeschlagen hast.
Noch andere Ideen?
Gruß RObert
-
-
left join steht im widerspruch zu alles != NULL.
daher ist ein INNER JOIN das richtige.
wie von anderen bereits gesagt: alle spalten, welche zum suchen benutzt werden sollten indiziert sein. hierzu gehören alle spalten in der where und besonders auch in der on bedingung.-
Hallo,
left join steht im widerspruch zu alles != NULL.
Wo hat er denn alles!=Null als Bedingung?
daher ist ein INNER JOIN das richtige.
Unsinn. Mit einem INNER JOIN könnte die Bedingung
B.auf_index IS NULL
niemals erfüllt werden.viele Grüße
Axel
-
ich habe mir nicht alles durchgelesen aber zumindest dies gesehen:
'
Wie kann man die Abfrage optimieren? ich will nur Datensätze wo ein auf_auflagentermin!=NULL und ba_auflagentermin!=NULL vorhanden ist.
'ein left oder right join gibt immer alle sätze einer tabelle zurück, auch wenn diese keine sätze in der anderen tabelle hat, also die on bedingung nicht erfüllt werden kann. in diesem fall werden die fehlenden mit NULL geliefert.
ein inner join liefert alle sätze, welche die on bedingung erfüllen. deshalb wird hier niemals NULL erscheinen.
-
Hallo,
ein left oder right join gibt immer alle sätze einer tabelle zurück, auch wenn diese keine sätze in der anderen tabelle hat, also die on bedingung nicht erfüllt werden kann. in diesem fall werden die fehlenden mit NULL geliefert.
Ja, richtig. Aber das heißt nicht, dass die Felder auf_auflagentermin bzw. ba_auflagentermin _deswegen_ den Wert NULL haben. Der kann auch schon in den einzelnen Tabellen drin gestanden haben. Sie können also auch NULL enthalten, obwohl die JOIN-Bedingung erfüllt ist, denn in ON stehen sie ja nicht drin.
ein inner join liefert alle sätze, welche die on bedingung erfüllen. deshalb wird hier niemals NULL erscheinen.
Doch. Die Felder, welche den Wert NULL in den einzelnen Tabellen enthalten, behalten natürlich den Wert NULL. Welche sollten Sie sonst bekommen?
viele Grüße
Axel
-
nochmal zum verständnis: wir reden hier nur von joins und on bedingung.
wenn in anderen datenspalten etwas enthalten ist, hat das hierauf keinen einfluß.
auch die where bedingung hat hiermit nichts zu tun. diese dient nur zur auswahl der relevanten zeilen.
ein join wird immer mit der on bedingung verknüpft. der unterschied zwischen left/right join und inner join liegt nur in der schnittmenge. ein join erweitert üblicherweise die spaltenanzahl der ergebnismenge, welche sonst nur aus einer der tabellen geliefert werden kann. deshalb wird mit dem join eine virtuelle breitere tabelle erstellt, aus der dann die ergebnismenge geliefert wird.
um hier die virtuelle länge der tabelle zu bestimmen, ist die art des joins zu benennen. ein inner join liefert immer die kleinste anzahl von zeilen. der left/right join liefert immer alle zeilen der left oder right tabelle, wobei die spalten der anderen tabellen bei nichtzutreffender on bedingung mit NULL gefüllt werden. (also kein korrespondierender satz in der anderen tabelle enthalten ist).gemäß der where bedingung werden nun die sätze aus virtuellen tabelle als ergebnismenge geliefert.
wenn man nun bewußt auf NULL bedingungen abfragt, liefert die ergebnismenge selbstverständlich auch NULL ergebnisse.
-
Hallo,
Das, was Du bis hierher sagst, ist alles richtig.
wenn man nun bewußt auf NULL bedingungen abfragt, liefert die ergebnismenge selbstverständlich auch NULL ergebnisse.
Das stimmt so nicht, wenn Du meinst, dass jede WHERE-Bedingung, welche prüft ob ein Feld den Wert NULL enthält, nur keine Datensätze, also eine NULL-Menge, als Resultat liefern kann. Das stimmt, beim INNER JOIN und den Feldern, die den JOIN bilden, also nach ON verglichen werden. Das stimmt nicht bei allen anderen Feldern. Beim OUTER-JOIN können selbst die Felder, die nach ON verglichen werden, im Resultset NULL-Werte enthalten.
viele Grüße
Axel
-
sorry, ich kann deinen gedankengängen nicht folgen.
beschäftige mich leider erst seit ca 30 jahren mit diesem metier.
bei meinen unzähligen sqls mit db2, oracle und co. klappt aber alles bestens.daher vielen dank für deine tipps.
-
sorry, ich kann deinen gedankengängen nicht folgen.
beschäftige mich leider erst seit ca 30 jahren mit diesem metier.
bei meinen unzähligen sqls mit db2, oracle und co. klappt aber alles bestens.daher vielen dank für deine tipps.
Vielen Dank erstmal für die Diskussion. Also das Statement liefert ohne eine Where Bedingung alle Datensätze der auftrag Tabelle und auch alle Datensätze der ba Tabelle. Soweit so gut.
SELECT A.auf_index, .. B.ba_lfnr, B.ba_status_tsystems, B.ba_datum, B.ba_arbeitsvermerk, B.ba_auflagentermin, B.ba_auflagentermin_ok FROM auftrag A LEFT JOIN ba B ON A.auf_index=B.auf_index ORDER BY B.ba_auflagentermin desc, A.auf_index desc;
Er braucht aber ca 10 Sekunden um die Zeilen zu liefern. Kann man vor der Where Bedingung die Menge eingrenzen? Ich brauche nur die Spalten die in A.auf_auflagentermin und B.ba_auflagentermin ein Datum haben. Und erst daraus will ich mit der Where Bedingung das Ergebnis wunschgemäss einschränken.
Geht das? Und wie?
-
Hallo,
SELECT A.auf_index, .. B.ba_lfnr, B.ba_status_tsystems, B.ba_datum, B.ba_arbeitsvermerk, B.ba_auflagentermin, B.ba_auflagentermin_ok FROM auftrag A LEFT JOIN ba B ON A.auf_index=B.auf_index ORDER BY B.ba_auflagentermin desc, A.auf_index desc;
Er braucht aber ca 10 Sekunden um die Zeilen zu liefern.
Also die Abfrage da oben dauert 10 Sekunden? Das kann dann nur an der fehlenden Indizierung von B.ba_auflagentermin liegen. A.auf_index ist ein Primärschlüssel, ist also in jedem Fall indiziert.
Liegt auf B.ba_auflagentermin ein Index? http://www.mysql.de/doc/de/SHOW_DATABASE_INFO.html
http://www.mysql.de/doc/de/CREATE_INDEX.htmlWenn es diesen Index geben sollte, kannst Du noch http://www.mysql.de/doc/de/OPTIMIZE_TABLE.html versuchen, um die Tabellen zu defragmentieren.
Sonst dauert es eben so lange. Überlege, ob Du den Outer-Join wirklich brauchst, also, ob Du die Datensätze aus Tabelle A brauchst, denen keine Datensätze aus Tabelle B zugeordnet werden können. Die Aussage:
"Ich brauche nur die Spalten die in A.auf_auflagentermin und B.ba_auflagentermin ein Datum haben."
hört sich _nicht_ danach an, es sei denn Du meinst:
Ich brauche nur die _Datensätze_, die in A.auf_auflagentermin _ODER_ B.ba_auflagentermin ein Datum haben.viele Grüße
Axel
-
Danke!
Leute Ihr seit Spitze, die Lösung war wirklich ein Index auf auf_index in der Tabelle ba. Alle anderen Felder habe ich indiziert aber auf_index nicht da es ein foreign Key war, dachte ich das Mysql ihn selbsständig indiziert. Aber weit gefehlt.
Wunderbar
DANKE
-
Hallo,
Die Lösung war wirklich ein Index auf auf_index in der Tabelle ba.
Ja, den hatte ich jetzt auch übersehen ;-))
Alle anderen Felder habe ich indiziert aber auf_index nicht da es ein foreign Key war, dachte ich das Mysql ihn selbsständig indiziert. Aber weit gefehlt.
Nein, ich glaube Indexe auf Fremdschlüssel setzen nur solche DBMS, die wirklich mit ForeignKeys, Tabellenbeziehungen und Prüfung referentieller Integrität arbeiten. Für MySQL ist das einfach ein Feld. Erst die Verknüpfung in SQL-Joins macht es zum Fremdschlüssel.
viele Grüße
Axel
-
-
-
-
-
-
-
-
-
-
-
wo ist eigentlich B.auf_index ????
'Kann man die Verknüpfung vor der Where Clausel einschränken?'
ja, die where klausel ist schon etwas reichlich durcheinander.
sind die spalten auflagentermin in den beiden tabellen gleich? wenn ja solltest du diese für die on bedingung heranziehen und selbstverständlich mit einen index versehen. auch die sonstigen kriterien können alle in die on-bedingung.
-
Hallo,
auch die sonstigen kriterien können alle in die on-bedingung.
Aua! Nein! In die ON-Klausel gehört die JOIN-Bedingung, sonst _nichts_.
viele Grüße
Axel
-
was gehört zur join bedingung?
willst du dem fragesteller eigentlich helfen? oder was ist dein ziel?
-
-