MySQL: Existenz eines Datensatzes prüfen

[Hugo Egon Balder]

Hallo Forum,

wenn ich für die weitere Verarbeitung eines Skripts einfach nur wissen möchte, ob ein bstimmter Inhalt schon Teil eines Datensatzes in einer Tabelle ist, löse ich das so:

Ich beginne mit einem SELECT COUNT ... AS anzahl ... und speichere das Ergebnis in der Variablen $anzahl:

$result=$db->query("SELECT COUNT(*) AS `anzahl` FROM `...` WHERE ...");  
$anzahl=$result->fetch_object()->anzahl;

Danach mache ich dann eine if-Abfrage:

if($anzahl!=0)  
  {  
    // Verarbeitung für den Fall, dass es diesen Inhalt schon gibt...  
  }  
else  
  {  
    // Verarbeitung für den Fall, dass es diesen Inhalt nicht gibt...  
  }

Ich möchte eigentlich nur wissen, ob das die übliche Vorgehensweise ist, oder ob es da einen kürzeren/besseren/eleganteren Weg gibt.

Danke im Voraus für jede Antwort!

MfG

Hugo Egon Balder

[Matti Mäkitalo]

Hi,

wenn ich für die weitere Verarbeitung eines Skripts einfach nur wissen möchte, ob ein bstimmter Inhalt schon Teil eines Datensatzes in einer Tabelle ist, löse ich das so:
[…]
Ich möchte eigentlich nur wissen, ob das die übliche Vorgehensweise ist, oder ob es da einen kürzeren/besseren/eleganteren Weg gibt.

kommt drauf an.
a) wenn du im Falle "es gibt Datensätze" danach die Datensätze nochmal abfragst, dann ist es u.U. schneller, direkt das Ergebnis zu berechnen und dann einfach mit $resultSet->num_rows dessen Größe zu erhalten.

b) wenn es nur um "gibt es welche"/"gibt es keine" geht, dann sollte die Nutzung von EXISTS deine Queries beschleunigen, da die MySQL-Engine dein SQL-Query bereits beim ersten Fund beenden kann, während beim COUNT natürlicherweise alles ausgewertet werden muss.
  SELECT EXISTS(SELECT * FROM blaWHEREbla.blub) AS resultSetNotEmpty

Bis die Tage,
Matti

[Hugo Egon Balder]

Hallo Matti,

danke für die Antwort!

b) wenn es nur um "gibt es welche"/"gibt es keine" geht, dann sollte die Nutzung von EXISTS deine Queries beschleunigen, da die MySQL-Engine dein SQL-Query bereits beim ersten Fund beenden kann, während beim COUNT natürlicherweise alles ausgewertet werden muss.
  SELECT EXISTS(SELECT * FROM blaWHEREbla.blub) AS resultSetNotEmpty

Mit EXISTS habe ich noch nie gearbeitet. Gehe ich recht in der Annahme, dass in diesem Fall das "resultSetNotEmpty" entweder den Wert 0 (für FALSE) oder den Wert 1 (für TRUE) hat?

MfG

Hugo Egon Balder

[dedlfix]

Tach!

Mit EXISTS habe ich noch nie gearbeitet. Gehe ich recht in der Annahme, dass in diesem Fall das "resultSetNotEmpty" entweder den Wert 0 (für FALSE) oder den Wert 1 (für TRUE) hat?

Wenn du schon nicht mit Unit-Tests versuchst, robusten Code zu schreiben, dann solltest du wenigstens im Laufe der Entwicklung mit Debug-Ausgaben die beiden Fälle provozieren und dir deren genaues Ergebnis anschauen.

Abgesehen davon hast du ja nun deinen Anwendungsfall offenbart. Eine Lösung kann man immer nur bewerten, wenn man die Aufgabenstellung kennt. Es ist völlig richtig, dass ein EXISTS die Suche nach dem ersten Vorkommen abbrechen kann. Bringt dir das aber in deinem Fall einen Vorteil? Immerhin musst du dafür eine Query mit Subquery erstellen. Wieviele Daten werden denn schätzungsweise in der Tabelle zu liegen kommen? Wenige? Millionen? Selbst wenn es viele sind, kann die Performance ganz hervorragend sein, wenn zum Beispiel ein Index bei der Suche helfen kann. Ich denke, du kannst die Lösung mit EXISTS im Hinterkopf behalten und darauf zurückkommen, wenn du an dieser Stelle Performanceprobleme bemerkst. Natürlich kannst du auch gleich EXISTS verwenden - zum Preis von etwas mehr Komplexität.

dedlfix.

[Hugo Egon Balder]

Hi dedlfix,

Wenn du schon nicht mit Unit-Tests versuchst, robusten Code zu schreiben,

ich nehme ja nicht an, dass das so unfreundlich gemeint war, wie es für mich jetzt klingt. =) Abgesehen davon ... ich verstehe den Satz nicht. Unit-Tests?

dann solltest du wenigstens im Laufe der Entwicklung mit Debug-Ausgaben die beiden Fälle provozieren und dir deren genaues Ergebnis anschauen.

Hatte ich jetzt Recht mit der Annahme, dass da entweder 0 oder 1 rauskommt - oder nicht?

Selbst wenn es viele sind, kann die Performance ganz hervorragend sein, wenn zum Beispiel ein Index bei der Suche helfen kann.

Das verstehe ich nicht ganz. Um bei meinem Beispiel zu bleiben: Wenn die Tabelle eine Spalte ID hat (Primärindex) und eine Spalte mit gesperrten Mailadressen und ich jetzt überprüfe, ob eine bestimmte Mailadresse in der Tabelle vorhanden ist - inwiefern soll/kann mir der Primärindex ID jetzt behilflich sein?

Ich denke, du kannst die Lösung mit EXISTS im Hinterkopf behalten und darauf zurückkommen, wenn du an dieser Stelle Performanceprobleme bemerkst. Natürlich kannst du auch gleich EXISTS verwenden - zum Preis von etwas mehr Komplexität.

Verstehe ich Dich richtig, dass für meinen gegebenen Fall die Art, wie ich es im Ursprungsposting gelöst habe, in Ordnung ist?

MfG

Hugo Egon Balder

[dedlfix]

Tach!

Wenn du schon nicht mit Unit-Tests versuchst, robusten Code zu schreiben,
ich nehme ja nicht an, dass das so unfreundlich gemeint war, wie es für mich jetzt klingt. =) Abgesehen davon ... ich verstehe den Satz nicht. Unit-Tests?

Das ist eine Vorgehensweise, bei der man zuerst definiert, wie sich ein bestimmer Code-Teil (Unit) zu verhalten hat, was er in den verschiedenen Fällen inklusive Fehlerfällen für ein Ergebnis liefern soll. Diese Definition kippt man in kleine Codehäppchen, für jeden zu testenden Fall eins. Jetzt erst schreibt man den eigentlichen produktiven Code und lässt immer wieder den Testcode laufen, bis alles "grün" ist. Das ist natürlich aufwendig, zahlt sich aber später aus, weil man durch die Tests schnell feststellen kann, wenn man etwas inkompatibles in den Code eingebaut hat.

Allerdings ist ein Unit-Test in deinem Fall mit Vorsicht zu genießen, weil er gegen ein externes System (das DBMS) gefahren werden müsste. Das macht man nicht, weil man nicht das exteren System testen will.

Wie auch immer, das war jetzt keine Ermunterung zum TDD (Test Driven Design), sonden nur mal so erwähnt.

dann solltest du wenigstens im Laufe der Entwicklung mit Debug-Ausgaben die beiden Fälle provozieren und dir deren genaues Ergebnis anschauen.
Hatte ich jetzt Recht mit der Annahme, dass da entweder 0 oder 1 rauskommt - oder nicht?

Darauf kommt es nicht an, sondern was das System liefert. Ich nehme an, es ist so, weil MySQL nicht wirklich boolesche Werte kennt sondern dafür 0 und 1 nimmt.

Selbst wenn es viele sind, kann die Performance ganz hervorragend sein, wenn zum Beispiel ein Index bei der Suche helfen kann.
Das verstehe ich nicht ganz. Um bei meinem Beispiel zu bleiben: Wenn die Tabelle eine Spalte ID hat (Primärindex) und eine Spalte mit gesperrten Mailadressen und ich jetzt überprüfe, ob eine bestimmte Mailadresse in der Tabelle vorhanden ist - inwiefern soll/kann mir der Primärindex ID jetzt behilflich sein?

Der nicht, aber ein (weiterer) Index auf der Mailadressen-Spalte. Den wirst du vermutlich in der Ausprägung Unique Index sowieso brauchen, um doppelte Einträge zu verhindern.

Ich denke, du kannst die Lösung mit EXISTS im Hinterkopf behalten und darauf zurückkommen, wenn du an dieser Stelle Performanceprobleme bemerkst. Natürlich kannst du auch gleich EXISTS verwenden - zum Preis von etwas mehr Komplexität.
Verstehe ich Dich richtig, dass für meinen gegebenen Fall die Art, wie ich es im Ursprungsposting gelöst habe, in Ordnung ist?

Ja, wenn die genannten Bedingungen zutreffen.

dedlfix.

[Hugo Egon Balder]

Hi dedlfix,

Unit-Tests?

Das ist eine Vorgehensweise, bei der man zuerst definiert, wie sich ein bestimmer Code-Teil (Unit) zu verhalten hat, was er in den verschiedenen Fällen inklusive Fehlerfällen für ein Ergebnis liefern soll.

Alles klar!

Das verstehe ich nicht ganz. Um bei meinem Beispiel zu bleiben: Wenn die Tabelle eine Spalte ID hat (Primärindex) und eine Spalte mit gesperrten Mailadressen und ich jetzt überprüfe, ob eine bestimmte Mailadresse in der Tabelle vorhanden ist - inwiefern soll/kann mir der Primärindex ID jetzt behilflich sein?

Der nicht, aber ein (weiterer) Index auf der Mailadressen-Spalte. Den wirst du vermutlich in der Ausprägung Unique Index sowieso brauchen, um doppelte Einträge zu verhindern.

Verstehe ich Dich richtig? Wenn ich mit phpMyAdmin eine Tabelle erstelle und dort bei einer Spalte 'Index' oder 'Unique' aktiviere, dann liefert mir das Durchstöbern der Spalte mit einem SELECT oder SELECT COUNT ein _schnelleres_ Ergebnis? Oder hab ich das falsch verstanden?

MfG

Hugo Egon Balder

[Jonny 5]

Hallo,

$anzahl

Das $result abzuholen ist in diesem Fall eigentlich gar nicht nötig. Man kann das zB auch so machen:

  
SELECT 1 FROM `my_tab` WHERE `col` LIKE 'foo%' LIMIT 1  

Nimm irgendwas, wenn es foo% schon gibt. Wenn das result eine Reihe hat (limit 1) dann existiert der gewünscht Datensatz:

  
$exists = ($result && (mysql_num_rows($result)==1));  

'Index' oder 'Unique' aktiviere, dann liefert mir das Durchstöbern der Spalte mit einem SELECT oder SELECT COUNT ein _schnelleres_ Ergebnis? Oder hab ich das falsch verstanden?

COUNT kann auch bei der Verwendung eines Indexes wenig sinnvoll sein, ausser du benötigst tatsächlich die Anzahl der Übereinstimmungen. Mit Treffer zählen kann die Abfrage deutlich länger dauern, da ja die gesamte Spalte verarbeitet werden muss. Ansonsten wird bei dem erstbesten Ergebnis abgebrochen.

Ein Index für das Beschleunigen ist immer sinnvoll, wenn es keine Wildcard gibt, oder die Wildcard nicht am Anfang steht, da sonst ev intern der gewünschte Index nicht verwendet werden kann, zB: 'fo%' => ok, 'f%o' => ok, braucht länger, '%oo' => Index macht keinen Sinn. Um zu wissen, ob der Index auch wunschgemäß funktioniert verwendet man explain

Bei unique-Geschichten kann natürlich die ON DUPLICATE KEY UPDATE Syntax praktisch sein.

[dedlfix]

Tach!

Verstehe ich Dich richtig? Wenn ich mit phpMyAdmin eine Tabelle erstelle und dort bei einer Spalte 'Index' oder 'Unique' aktiviere, dann liefert mir das Durchstöbern der Spalte mit einem SELECT oder SELECT COUNT ein _schnelleres_ Ergebnis? Oder hab ich das falsch verstanden?

Normalerweise ist das die Aufgabe eines Index. Der Unique-Index hat zudem die Aufgabe, für Eindeutigkeit zu sorgen.

Der Index ist ja eine sortierte Menge, weswegen Zugriffe darauf sehr schnell ausgeführt werden können. Mitunter kann das Ergebnis einer Abfrage gar rein unter der Verwendung eines Index ermittelt werden. Ein COUNT() muss nicht die gesamte Tabelle durchsuchen, wenn laut Index kein weiterer Datensatz mehr gefunden werden kann. Beim Unique-Index wird es sogar noch deutlicher, dass es genau einen Datensatz gibt oder keinen. COUNT() kann also genauso gleich abbrechen wie ein EXISTS in einer unsortierten Menge. EXISTS ohne Index muss aber zumindest von vorn an jeden Datensatz absuchen, bis es einen passenden gefunden hat. Mit dem Index dürften sich beide Abfragen zeitlich nicht mehr viel nehmen. COUNT() kann lediglich dann deutlich langsamer sein, wenn eine sehr große Menge mit demselben Wert zu zählen wäre, aber dann ist vielleicht auch der Index wenig hilfreich.

All das sind nur theoretische Betrachtungen gepaar mit etwas Erfahrung. Der Optimizer in MySQL kann zur Abarbeitung einer Abfrage auch eine ganz andere eigene Meinung haben. Einen vorhanden Index zu verwenden lohnt sich zum Beispiel nicht, wenn nur drei Datensätze in der Tabelle sind. Da ist ein Full-Table-Scan auch nicht langsamer. Du kannst mit vorangestelltem EXPLAIN ergründen, was MySQL bei einer Query so zu tun gedenkt. Die Antwort ist aber auch stark vom Datenbestand abhängig und sie wird bei drei Datensätzen anders ausfallen als bei drei Millionen. Eine EXPLAIN-Betrachtung kann also nicht unbedingt verallgemeinert werden und sollte immer unter realistischen Bedingungen stattfinden, nicht nur mit einem sehr kleinen Testdatenbestand.

dedlfix.

[dedlfix]

Tach!

wenn ich für die weitere Verarbeitung eines Skripts einfach nur wissen möchte, ob ein bstimmter Inhalt schon Teil eines Datensatzes in einer Tabelle ist, löse ich das so:

Neben der von Matti aufgezeichneten Möglichkeit kann sich auch ein ganz anderes Vorgehen erforderlich machen. Wenn du in zwei getrennten Vorgängen prüfst um dann aufgrund dieses Ergebnisses eine Änderung vorzunehmen, kann sich in der Zwischenzeit die Datenbasis durch nebenläufige Prozesse bereits geändert haben. Das ist ein so genanntes TOCTTOU-Problem. Was die beste Lösung dafür ist, kommt auf das konkrete Anliegen an. Vielleicht hilft INSERT ... ON DUPLICATE KEY UPDATE oder ein INSERT mit externer Prüfung auf eine Ducplicate-Key-Verletzung.

dedlfix.

[Hugo Egon Balder]

Hi Dedlfix,

danke für die Antwort!

Wenn du in zwei getrennten Vorgängen prüfst um dann aufgrund dieses Ergebnisses eine Änderung vorzunehmen, kann sich in der Zwischenzeit die Datenbasis durch nebenläufige Prozesse bereits geändert haben. Das ist ein so genanntes TOCTTOU-Problem. Was die beste Lösung dafür ist, kommt auf das konkrete Anliegen an.

Ich denke, das betrifft mich in meinem konkreten Fall nicht. Ich experimentiere gerade mit der php Mailfunktion und arbeite dabei an einem Formular, wo dafür eine Mailadresse eingegeben werden muss. In der DB existiert eine Tabelle, in der sich gesperrte Mailadressen befinden und bei der Formularverarbeitung wird einfach überprüft, ob die eingegebene Mailadresse gesperrt ist oder nicht - sprich - ob das Skript die Mailadresse weiterverabeiten darf oder nicht.

MfG

Hugo Egon Balder