Zu langsame Abfrage

[Tob]

Hi,

In meinem mysql-slowquery log taucht eine Abfrage auf, die mir die nächste freie Kundennummer raussuchen soll. Der Bereich ist zwischen 10000 und 70000 abgesteckt.

  
SELECT MIN( Kundennummer )  
FROM kunden t1  
WHERE NOT  
EXISTS (  
SELECT Kundennummer  
FROM kunden t2  
WHERE t2.Kundennummer = t1.Kundennummer +1  
)  
AND  
t1.Kundennummer > 10000 AND  
t1.Kundennummer < 70000;  

Kann man die optimieren, bzw. manchmal habe ich sogar den Eindruck, sie macht gar nicht, was sie soll. D.H. sucht sie mir so wirklich den nächsten freien Kundennummernplatz oder habe ich mich da völlig verstrickt?

Freundliche Grüße

Tobias

[LX]

Willst Du wirklich Kundennummern überschreiben und so das Problem haben, dass Rechnungen gelöschter Kunden intern plötzlich unter dem neuen Kunden auftauchen? Hier wäre eine unique ID m.E. sinnvoller.

Gruß, LX

[Tob]

Willst Du wirklich Kundennummern überschreiben und so das Problem haben, dass Rechnungen gelöschter Kunden intern plötzlich unter dem neuen Kunden auftauchen? Hier wäre eine unique ID m.E. sinnvoller.

Hi LX,

Kunden werden bei mir nicht gelöscht, sondern erhalten ein delete-flag.

Freundliche Grüße, Tobias

[Frank (no reg)]

Auch dann (oder dann erst recht) gibt es keinen wirklichen Grund für das "gib mir die kleinste freie Kundennummer". Denn dieses sollte von einem auto-numerischen Wert (stichwort auto increment) bereits und viel effizienter abgedeckt sein.

Gruss, Frank

[T-Rex]

Auch dann (oder dann erst recht) gibt es keinen wirklichen Grund für das "gib mir die kleinste freie Kundennummer". Denn dieses sollte von einem auto-numerischen Wert (stichwort auto increment) bereits und viel effizienter abgedeckt sein.

Gruss, Frank

Jap genau,
Wenn du die auto_increment Einstellung benutzt und zudem nichts löscht wie du selber sagst, dann sollte die nächste Höhere Zahl immer die freie Kundennummer sein.
Ansonsten macht SQL in deinem Fall 3 komplette Datenbank Scans. Einmal holt es sich die Daten für die Tabelle die du durchsuchen willst. Dann nochmal für den Subquery (wobei das ein wenig schneller gehen sollte, da die Daten bereits im Cache liegen. Und dann nochmals für die Range im Where. Zudem kann kein Index benutzt werden, da Range keinen Index benutzt.
Die Idee zur Optimierung wäre bei einer MyIsam Tabelle, dass du ALLE Daten in die Programmiersprache holst und dort die kleinstmögliche Kundennummer suchst. Zudem könntest du die zuletzt gefundene kleinste Kundennummer speichern, dann kannst du bei deiner nächsten Abfrage ab dieser Zahl suchen.

Beispiel in Pseudocode:
--------------
$arDaten = "select * from kunden where kundennummer > kleinste_zuletzt_gefundene_nummer"
foreach($arDaten)
{
   $kleineKundenummer++
   if($kleineKundenummer != $arDaten[$i]['kundenummer'])
   break;
}

echo "dies ist die kleinste Kundenummer: ".$kleineKundenummer;

speichere_kleine_Kundenummer($kleineKundenummer);
--------------

Gruß
T-Rex

[Tob]

Jap genau,
Wenn du die auto_increment Einstellung benutzt und zudem nichts löscht wie du selber sagst, dann sollte die nächste Höhere Zahl immer die freie Kundennummer sein.
Ansonsten macht SQL in deinem Fall 3 komplette Datenbank Scans. Einmal holt es sich die Daten für die Tabelle die du durchsuchen willst. Dann nochmal für den Subquery (wobei das ein wenig schneller gehen sollte, da die Daten bereits im Cache liegen. Und dann nochmals für die Range im Where. Zudem kann kein Index benutzt werden, da Range keinen Index benutzt.
Die Idee zur Optimierung wäre bei einer MyIsam Tabelle, dass du ALLE Daten in die Programmiersprache holst und dort die kleinstmögliche Kundennummer suchst. Zudem könntest du die zuletzt gefundene kleinste Kundennummer speichern, dann kannst du bei deiner nächsten Abfrage ab dieser Zahl suchen.

Hallo T-Rex,

aah, danke Dir. Genau nach dieser Erklärung und einem solchen Tip habe ich gesucht.

Damit sehe ich meine Frage auch schon als vollständig beantwortet an.

Vielen Dank an Dich, Tobias

[dedlfix]

Hi!

Ansonsten macht SQL in deinem Fall 3 komplette Datenbank Scans.

Woher weißt du, dass der OP keinen Index verwendet?

Einmal holt es sich die Daten für die Tabelle die du durchsuchen willst. Dann nochmal für den Subquery (wobei das ein wenig schneller gehen sollte, da die Daten bereits im Cache liegen.

Höchstens im Filesystem-Cache, denn ich kann mir nicht vorstellen, dass sie im Query-Cache von MySQL liegen, weil die Subquery jedesmal eine andere ist und nur bei Übereinstimmung auf den Query-Cache zugegriffen werden kann und wird.

Und dann nochmals für die Range im Where. Zudem kann kein Index benutzt werden, da Range keinen Index benutzt.

Du meinst das außere WHERE? In meinem nachgestellten Szenario zeigte EXPLAIN nämlich für die Subquery ein nettes "Using index" an. Natürlich hatte ich einen auf die Kundennummer gelegt.

Und wie entstehen jetzt drei Full Table Scans (angenommen, du meinst das mit "komplette Datenbank Scans")? Ein FROM erzeugt doch noch keinen Scan, oder?. Beim Abarbeiten des äußeren WHERE kommt möglicherweise ein Full Table Scan zum Einsatz. Da hat mir das EXPLAIN keine Indexverwendung angezeigt, was aber auch an meinem zu geringen Testdatenbestand von 5 Datensätzen gelegen haben könnte. Ich denke aber, dass für den beiden Einschränkungen sehr wohl ein Index verwendet werden könnte. Jedenfalls hat die Subquery ja (trotz der 5 Datensätze) einen Index verwendet, da findet also auch kein Full Table Scan statt. Zudem würde er ja bei jeder Subquery-Abarbeitung notwendig sein, weswegen deine 3 nur in einem einzigen Fall stimmen würde.

Die Idee zur Optimierung wäre bei einer MyIsam Tabelle, dass du ALLE Daten in die Programmiersprache holst und dort die kleinstmögliche Kundennummer suchst.

Ich würde ja erst einmal die (nicht) vorhandenen Index untersuchen und die EXPLAIN-Ausgabe analysieren, bevor ich alle Daten antanzen lasse. Außerdem könnte man sowas mit einer Stored Procedure auch gleich im DBMS erledigen lassen.

Lo!

[Tob]

Auch dann (oder dann erst recht) gibt es keinen wirklichen Grund für das "gib mir die kleinste freie Kundennummer". Denn dieses sollte von einem auto-numerischen Wert (stichwort auto increment) bereits und viel effizienter abgedeckt sein.

Das ist es auch und nennt sich Kunden-ID. Diese wird für alle inner-db-mäßigen relationalen Verknüpfungen genutzt und kann seitens des Kunden nicht geändert werden.

Aber es beantwortet meine Frage leider nicht. Denn eine Kundennummer ist nunmal nichts so ungewöhnliches und diese Kundennummer muß seitens des Nutzers in einem gewissen Rahmen frei bestimmbar sein. Dennoch würde ich ihm gerne die "nächst freie" Nummer anbieten.

Könnten wir uns denn nun mal ausschließlich darüber unterhalten und nicht weiter mein System selber in Frage stellen? Denn das passt so schon.

Freundliche Grüße, Tobias

[dedlfix]

Hi!

Aber es beantwortet meine Frage leider nicht.

Es sind durchaus schon deutlich bessere Lösungen herausgekommen, wenn man versucht, das eigentliche Problem zu lösen und nicht nur stur auf eine konkrete Fragestellung einzugehen, die ein Problem lösen soll, in dass sich jemand verrannt hat, das es bei einer generell besseren Lösung gar nicht gäbe. Einige sind über Alternativen erfeut, andere nicht. Man kann das als Antwortender nicht vorhersehen, weswegen du, der du solche alternativen Lösungen anscheinend nicht haben möchtest, trotzdem mit solchen Antworten Vorlieb nehmen musst. Zudem ist es nicht ganz schlecht, wenn auch die Leser im Archiv Hinweise auf Alternativen zu lesen bekommen.

Denn eine Kundennummer ist nunmal nichts so ungewöhnliches und diese Kundennummer muß seitens des Nutzers in einem gewissen Rahmen frei bestimmbar sein. Dennoch würde ich ihm gerne die "nächst freie" Nummer anbieten.
Könnten wir uns denn nun mal ausschließlich darüber unterhalten und nicht weiter mein System selber in Frage stellen? Denn das passt so schon.

Dann musst du aber auch diese Anforderungen bekanntgeben. Denn wenn sich der Kunde seine Nummer nicht beliebig wählen dürfte, ist es durchaus legitim, von einer automatischen Vergabe auszugehen und die einfachere und wesentlich effizientere Lösung mit dem auto_increment vorzuschlagen.

Aber zu deiner Frage: Was sagt denn EXPLAIN und gibt es überhaupt einen Index auf der Kundennummer, der verwendet werden könnte? Zudem ermittelt deine im OP gezeigtes Statement nicht die nächste freie sondern die Kundennummer, nach der eine Lücke ist. Da ist wohl ein +1 verloren gegangen oder es wird anderswo hinzugefügt.

Lo!

[reborn]

Hi Tob,

was für'n Halsbrecher. Nutze Where zum Filtern. Was soll geschehen, wenn es keine freie Kundennummer gibt?

LG