MySQL- Prüfen ob IP in Userdatenbank schon vorhanden, trotzdem alle Nutzer ausgeben

[hansbiedemann]

Hallo Community,

ich habe folgende SQL Query "SELECT * FROM KUNDEN*

Damit liste ich alle Kunden der Datenbank auf mit 50 Stück pro Seite.

Danach überprüfe ich innerhalb der Schleife (jedoch nicht gut, es läd sehr lange), ob der Nutzer schonmal mit der IP vorhanden ist "SELECT id FROM KUNDEN WHERE ip = $ipvomerstenquery"

Wenn num_rows hier größer als Null dann leuchtet ein roter Text auf, IP existiert schonmal in der DB.

Nun meine Frage, wie kann ich das gleich oben in den Query mit einbinden, dass er mit mit in die Schleife übergibt (als Spalte), ob der Kunde schonmal mit selbiger IP existiert? (Ohne Aber nur die auszugeben, die keine doppelte IP haben, oder umgekehrt, es müssen alle Kunden ausgegeben werden)

 

[sheel]

Hi

du willst also Kunden markieren, die mehr als eine Zeile in der Tabelle haben?
Und dieser rote Eintrag usw., ist das mit PHP?

 

[cwriter]

Nun meine Frage, wie kann ich das gleich oben in den Query mit einbinden, dass er mit mit in die Schleife übergibt (als Spalte), ob der Kunde schonmal mit selbiger IP existiert? (Ohne Aber nur die auszugeben, die keine doppelte IP haben, oder umgekehrt, es müssen alle Kunden ausgegeben werden)

"Ob" ist relativ leicht (aus dem Kopf, kann also leichtere Syntaxfehler beinhalten):

    SELECT DISTINCT id AS idOne, ip AS ipOne FROM KUNDEN WHERE ip = '127.0.0.1'
    AND COUNT(
        SELECT id FROM KUNDEN WHERE id = idOne AND ip = ipOne
    ) > 1

Alle Kunden:

    SELECT DISTINCT id AS idOne, ip AS ipOne
    , COUNT(
        SELECT id FROM KUNDEN WHERE id = idOne AND ip = ipOne
    ) as cnt
    FROM KUNDEN WHERE ip = '127.0.0.1'

Das gibt dir alle User ID und IPs aus, die mehr als einmal vorhanden sind. Allerdings zeigt das relativ grosse Probleme mit deinem Schema; es scheint ziemlich 1NF zu sein - in "KUNDEN" hat nur die Id und ggf. die zugehörigen, direkten Daten, wie Namen oder so zu sein.
Mit IP-Adressen hast du ein Problem, da unique nicht funktionieren kann (ausser du machst den Key aus ID und IP, was aber aufgrund der Frage nicht der Fall ist).

Danach überprüfe ich innerhalb der Schleife (jedoch nicht gut, es läd sehr lange), ob der Nutzer schonmal mit der IP vorhanden ist "SELECT id FROM KUNDEN WHERE ip = $ipvomerstenquery"

Wie viele hundert tausend Einträge hast du denn?
Und das

"SELECT id FROM KUNDEN WHERE ip = $ipvomerstenquery"

Steht hoffentlich nicht so im PHP-Code (SQL-Injections...)

Gruss
cwriter

 

[Biber3]

Moin hansbiedemann,

kurze Antwort:

SELECT k.*
  , (Select count(*) FROM Kunden
    WHERE IP=k.IP
   ) as AnzDupIPs
FROM Kunden k

Etwas längere Antwort:
So ein Inline-Select macht natürlich nur Spaß, wenn du ungefähr weisst, was der Kontext ist.
Wenn du 10000 Kunden-Datensätze hast, wird eben auch 10000x dieses SELECT Count(*) gemacht.
Also logischerweiser 10000x ein FULL TABLE SCAN über je 10000 Datensätze gemacht, falls nicht ein nutzbarer Index auf dem Feld IP liegt.
Unterstellen wir mal, dass auch bei den 10000 Datensätze nur 10 IPs doppelt vergeben sind, dann ist dieses IPs-Durchzählen für 99% aller Ausführungen für die Tonne (COUNT(*) ist 1, was ja ok ist).

Also wären zwei Überlegungen bezüglich Effizienz empfehlenswert:
a) Hey, wenn doppelte IPs "falsch" sind - kann ich die dann nicht in einem separaten Prozess durchzählen und diese dubiosen Fälle bereinigen?--> Wenn das klappt, dann nach der Bereinigung einen neuen UNIQUE-Index auf das Feld IP legen und es gibt niemals nich' wieder Probleme damit.

b) Wenn denn wenigstens ein Index (non-Unique) auf dem Feld-IP liegt, und größenordnungsmäßig nur so wenig Duplikate zu erwarten sind:

SELECT k.*
  , i.anz
FROM kunden k
LEFT JOIN
  ( SELECT IP, Count(*) as anz
    FROM Kunden
    GROUP BY IP
    Having Count(*) > 1
 ) i ON k.IP=i.IP

Dann steht in den paar Treffer-Datensätzen eine 2 oder 3 im Feld "Anz", sonst der Wert NULL.

Grüße
Biber

 

[Yaslaw]

Ich würde die 2te Variante von Biber in ein INNER JOIN wandeln.
Dann hast du die ANzahl Treffer, 1 bedeutet nur einmal vorhanden. etc.

SELECT k.*
  , i.anz
FROM kunden k
INNER JOIN
  ( SELECT IP, COUNT(*) AS anz
    FROM Kunden
    GROUP BY IP
 ) i ON k.IP=i.IP

 

[Biber3]

Moin Yaslaw,

na ja, da aber zwangsläufig zu JEDER vorhandenen IP eine Anzahl vorliegt und damit ein Datensatz, wäre es bei einem INNER JOIN eine Verknüpfung von 10000 Datensätzen aus KUNDEN alias k und 9990 Datensätzen aus KUNDEN alias i.

Beim LEFT JOIN mit HAVING() eine Verknüpfung von 10000 Datensätzen aus KUNDEN alias k und 10 Datensätzen aus KUNDEN alias i.
Bei gleichem Informationsgehalt. Stelle ich mir performanter vor.

Aber egal - ich habe nicht geprüft, ob es einen messbaren Unterschied macht bei der Ausführung.
In jedem Fall wäre zu empfehlen, einen Index auf das Feld IP zu legen.

Grüße
Biber

 

[Fragenfrager]

Was soll denn erreicht werden, wenn das Ergebnis vorliegt? Soll ein neuer Eintrag erfolgen, wenn die IP noch nicht vorliegt?
Dann könnte man lösen, indem man die IP beim INSERT mitgibt und direkt auf das Vorhandensein abfragt.
So vermeidest Du den Bruch SQL->PHP->SQL

INSERT INTO kunden
VALUES ('@IP','<andere Felder>')
WHERE NOT EXISTS
    (SELECT IP
    FROM kunden
   WHERE IP='@IP')

Je nachdem Result kannst Du dann in PHP eine Hinweistext auswerfen.
Grundsätzlich sollte man ein SELECT * vermeiden und nur die Spalten abfragen, die tatsächlich benötigt werden. Das ist zum einen besser für die Performance, zum anderen musst Du dann in der (PHP?)-Verarbeitung Deine Resultsets nicht anpassen, falls sich am Tabellenaufbau etwas ändert.
Grundsätzlich sollte aber vorher die Tabelle von Doubletten bereinigt werden. Der hier erwähnte Index würde helfen.