MySQL Abfrage langsam

[mixmastertobsi]

Hallo,

ich habe eine Frage und ein Problem zu folgender Abfrage, da diese sehr langsam ist. In der Tabelle Rechnung sind über 200.000 Datensätze. Wenn ich nun die Abfrage starte, sucht es in allen 200.000 Zeilen und schaut, ob ein Join Datensatz verfügbar ist. Wie kann ich das vereinfachen

SELECT COUNT(rechnungen.auftragid) as a1
FROM rechnungen
LEFT JOIN auftrag_info ON auftrag_info.auftragnr=rechnungen.auftragid AND auftrag_info.status='1' 
WHERE auftrag_info.date IS NULL

[pl]

zeig mal explain..

[mixmastertobsi]

Anbei

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	rechnungen	index		unique_index	4		199939	Using index
1	SIMPLE	auftrag_info	ref	auftragnr,index_type	auftragnr	4	auftrag_rechnung.auftragnr	2	Using where; Not exists

[Felix Riesterer]

Lieber mixmastertobsi,

Abfrage, da diese sehr langsam ist. In der Tabelle Rechnung sind über 200.000 Datensätze.

hast Du auch brav alle Schlüssel als solche eingerichtet?

rechnungen.auftragid auftrag_info.auftragnr rechnungen.auftragid

Sind diese wirklich als Indices eingerichtet?

Liebe Grüße,

Felix Riesterer.

[Rolf B]

Hallo mixmastertobsi,

wie ist denn die Kardinalität der Relation rechnungen - auftrag_info?

Du hast eine auftrag_id, also wird es wohl ein n:1 sein, aber welche Werte hat n? 1, 0..1, 1..n, 0..n?

Sprich: ist die Rechnung zum Auftrag-Info optional oder nicht? Kann es zu einer Auftrag-Info mehr als eine Rechnung geben? Davon mag es abhängen wie man die Query besser formulieren kann.

Die Fragen nach dem Index und den Explain werden dadurch aber nicht irrelevant!

Rolf

[mixmastertobsi]

Ja, in Auftrag_rechnung gibt es mehr als eine Rechnung, nämlich sind dort alle Rechnung_infos hinterlegt.

[Rolf B]

Hallo mixmastertobsi,

und jetzt nochmal von vorn, und bitte vollständig.

Deine Query zeigt Auftrag_Rechnung nicht. Von Rechnung_infos war auch keine Rede. Dein Explain passt nicht zur gezeigten Query.

Welche Query ist langsam und auf welchem Schema hantiert sie? Inklusive Kardinalitäten und Indexen bitte.

Rolf

[mixmastertobsi]

Also vereinfacht gesagt…

Ich habe eine Tabelle "rechnungen" und eine Tabelle "rechnungen_info".

In der Tabelle rechnungen habe ich die Rechnungsnummern (ca 200.000) Unique mit dem jeweiligen Rechnungsdatum.

In der Tabelle rechnung_info sind die weiteren Infos hinterlegt. Wenn ein Auftrag versandt wurde, wird in rechnung_info eine Zeile mit der Rechnungsnummer dem Datum und dem Type erzeugt.

in der Tabelle Rechnung gibt es folgende Spalten

ID | rechnungid | type | value

der Index ist dabei auf rechnungid und type gesetzt.

Nun möchte ich ALLE Rechnungen ausgeben lassen, bzw. die Menge ausgeben, wo noch nicht versandt wurde und eben in "rechnung_info" keine Zeile mit der Rechnungsnummer existiert.

[Felix Riesterer]

Lieber mixmastertobsi,

In der Tabelle rechnungen habe ich die Rechnungsnummern (ca 200.000) Unique mit dem jeweiligen Rechnungsdatum.

aha, ein Datum, das jeweilige Rechnungsdatum.

In der Tabelle rechnung_info [...] eine Zeile mit der Rechnungsnummer dem Datum und dem Type erzeugt.

Und noch ein Datum. Das Versanddatum der Rechnung, oder das Rechnungsdatum?

in der Tabelle Rechnung gibt es folgende Spalten

Also reden wir von einer Tabelle Rechnung, rechnungen und rechnung_info. Stimmt das so? Und wo ist Deine eingangs erwähnte Tabelle auftrag_info? Oder hast Du die inzwischen in rechnung_info umbenannt? Und warum notierst Du Rechnungen hier mit großem R anstatt mit kleinem r wie im OP?

ID | rechnungid | type | value

der Index ist dabei auf rechnungid und type gesetzt.

Aha, der Primary Key besteht aus den Schlüsseln rechnungid und type. Welche Rolle spielt da die Spalte ID?

Nun möchte ich ALLE Rechnungen ausgeben lassen, bzw. die Menge ausgeben, wo noch nicht versandt wurde und eben in "rechnung_info" keine Zeile mit der Rechnungsnummer existiert.

Welche Rechnungsnummer? Meinst Du rechnungid?

Für mich klingt das alles recht wirr und durcheinander. Bereite doch bitte einmal die Informationen so auf, dass unsereiner sie gleich beim ersten Lesen versteht! Dann kann man Dir auch helfen, anstatt diese lästigen Rückfragen stellen zu müssen. Du bist doch nun schon eine Weile dabei und kennst diesen Laden hier gut genug, um zu verstehen was ich meine.

Liebe Grüße,

Felix Riesterer.

[Regina Schaukrug]

Nun möchte ich ALLE Rechnungen ausgeben lassen, bzw. die Menge ausgeben, wo noch nicht versandt wurde und eben in "rechnung_info" keine Zeile mit der Rechnungsnummer existiert.

Wie Felix schon schrieb bringst Du selbst Deine Tabellen durcheinander. Du hattest:

SELECT COUNT(rechnungen.auftragid) as a1
FROM rechnungen
LEFT JOIN auftrag_info ON auftrag_info.auftragnr=rechnungen.auftragid AND auftrag_info.status='1' 
WHERE auftrag_info.date IS NULL

Gemäß Deinem Ansinnen müssen alle Zeilen in auftrag_info „durchgechelt“ werden um zu checken, ob auftrag_info.date NULL ist, respektive die Zeile existiert. Ich frage mich schon mal, warum Du nicht sachangemessen auftrag_info.auftragnr auf NULL prüfst...

„Selektiere alles von der linken Tabelle, auch wenn in der rechten kein übereinstimmender Wert vorhanden ist.“

Literatur

Also:

SELECT COUNT(rechnungen.auftragid) as a1
FROM rechnungen LEFT JOIN auftrag_info
ON auftrag_info.auftragnr=rechnungen.auftragid
WHERE auftrag_info.auftragnr IS NULL

[Rolf B]

Hallo Regina,

sachangemessener ist das, noch angemessener wäre ein NOT EXISTS statt einem LEFT JOIN.

Aber wenn ich mir den Explain anschaue, hat MYSQL den LEFT JOIN ohnehin schon zum NOT EXISTS umgebaut. Wenn die Datums-Spalte im Schema nicht nullable ist, wäre das eine naheliegende Optimierung.

Rolf

[Regina Schaukrug]

SELECT COUNT(rechnungen.auftragid) as a1
FROM rechnungen LEFT JOIN auftrag_info
ON auftrag_info.auftragnr=rechnungen.auftragid
WHERE auftrag_info.auftragnr IS NULL
AND auftrag_info.status='1'

Freilich kann man dann, wie Rolf B schreibt, mit explain mal nachsehen, was der Optimierer daraus bastelt.

[pl]

Nun möchte ich ALLE Rechnungen ausgeben lassen, bzw. die Menge ausgeben, wo noch nicht versandt wurde und eben in "rechnung_info" keine Zeile mit der Rechnungsnummer existiert.

Nun, ich denke, Du solltest Dir mal die Unterschiede zwischen left//right//inner/outer Join reinziehen.

MfG

[Rolf B]

Hallo mixmastertobsi,

Felix' Ermahnung kannst Du Dir durchaus zu Herzen nehmen, aber ich glaube, ich habe auch so schon einen Tipp für Dich. Ich weiß nur nicht, ob der Tipp wirklich greift; Deine „fachliche Beschreibung“ weicht doch deutlich von deiner Query ab.

Eigentlich wollte ich Dir ja vorschlagen, von einem LEFT JOIN auf eine NOT EXISTS Abfrage zu wechseln, aber der Explain zeigt, dass mysql das ohnehin schon für Dich gemacht hat. Insofern ist das für die Performance nicht hilfreich. Es könnte aber lesbarer sein und die Absicht der Query deutlicher zum Ausdruck bringen.

Verdächtig ist die "USING WHERE" Angabe in der 2. Zeile des Explain. Diese Angabe bedeutet, dass er außer dem Index-Zugriff noch in die Tabelle schauen muss, um zu prüfen, was da steht. D.h. er führt einen Dateizugriff mehr aus. Du könntest einen Vorteil gewinnen, wenn Du die Spalte Status mit in den Index der Info-Tabelle aufnimmst. Dann kann mysql die Exists-Prüfung allein über den Index befriedigen und muss nicht auf die Datentabelle zurückgreifen.

Die Frage ist aber, ob du nicht auch mit dem existierenden Index auskommst. Du hast ja beschrieben, dass Du prüfen möchtest, ob es zu einer Rechnung KEINEN SATZ IN DER INFO-TABELLE gibt. Deine Query prüft aber, ob es keinen Satz zur Rechnung gibt, der Status 1 hat. Wenn die Status Abfrage nicht wichtig ist, dann lass sie weg und du kannst den Index lassen wie er ist. Wenn doch - ok, dann könnte eine Indexerweiterung helfen.

Oder auch nicht. Problem an einer Index-Erweiterung KANN SEIN, dass damit Updates langsamer werden. Ein "Status" Attribut neigt dazu, sich öfter mal zu ändern. Ist es im Index, muss bei jedem Update auch der Index aktualisiert werden. Wenn der Index der clustered index ist, führt das zu größeren Datenschaufeleien weil er dann die Daten umsortiert. Das ist jetzt eine Abwägungssache. Eventuell musst Du neben den Clustered-Index noch einen Secondary Index setzen. Da hilft nur probieren und messen, welche DB-Operationen durch welche Indexe schneller und langsamer werden.

Query mit NOT EXISTS - sollte genau so schnell sein wie der LEFT JOIN.

select Count(*)
from Rechnungen r
WHERE NOT EXISTS (SELECT * FROM auftrag_info ai
                  WHERE ai.auftragnr = r.auftragnr AND ai.status = '1')

plusminus ein paar Anpassungen an deine persönliche Realität.

Rolf