MYSQL-Zugriff mit WHERE und HAVING?

TheUnknown · Beiträge: 334

Hallo,

ich verzweifel hier langsam...

Meine News-Datenbank hat diverse Felder, zwei davon heissen KONTEN_ID (Integer) und IP (TINYTEXT), außerdem das übliche wie TEXT, ID oder ZEITPUNKT... Wichtig sind aber nur KONTEN_ID und IP. Nun...

KONTEN_ID ist die ID des Benutzerkontos als Integerwert; also 1, 4 oder 17.

IP ist die IP-Adresse, die beim Speichern der News des Benutzerkontos genutzt wurde.

Ich möchte jetzt AUSSCHLIESSLICH und mit nur einer einzigen Abfrage diejenigen Datensätze haben, die auf IPs basieren, die von mindestens ZWEI verschiedenen Benutzerkonten benutzt worden sind. Hat Benutzer "4" also z.B. 10 News mit derselben IP geschrieben, aber ansonsten wurde die IP noch nie benutzt, soll das nicht im Suchergebnis sein. Wurde aber z.B. die IP-Adresse 1.2.3.4 von den beiden Benutzern "1" und "4" irgendwann einmal beim Newsspeichern einmal genutzt (egal, ob das Jahre oder Sekunden auseinander liegt), dann soll in den Suchergebnissen enthalten sein.

Ich habe es schon mit LIMIT, DISTINCT, GROUP BY...HAVING, Sub-SELECT-Klauseln und soweiter ausprobiert - immer in verschiedenen Konstallationen, aber es klappt alles nicht. Ich habe den Eindruck, wahllos einfach irgendwelche IPs herauszubekommen, was ich aber nicht haben will, sondern ja nur die IPs, die jeweils mehr als einmal von mehreren Usern benutzt wurden.

Hat da jemand eine Idee?

Martok · Verfasst: Do 06.09.12 16:37

Moin!

Muss das ganze performant sein, oder ist das eher so eine Einmal-Management-Abfrage?

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:

SELECT n.*
FROM news n
WHERE n.IP IN (
  SELECT DISTINCT s.IP FROM news s
  GROUP BY s.IP
  HAVING count(s.KONTEN_ID)>1
)

Ich hab das mal analog auf die DB eines anderen Projekts losgelassen - das ist schweinemäßig langsam, weil MySQL für IN (SELECT) den Index auf IP nicht nutzt, aber funktioniert

Würdest du das Subselect "ausmultiplizieren", also die Ergebnismenge direkt als IN (123,345,567) schreiben, wäre das schnell.

_________________
"The phoenix's price isn't inevitable. It's not part of some deep balance built into the universe. It's just the parts of the game where you haven't figured out yet how to cheat."

TheUnknown · Beiträge: 334

Martok hat folgendes geschrieben :

Moin!

Muss das ganze performant sein, oder ist das eher so eine Einmal-Management-Abfrage?

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:

SELECT n.*
FROM news n
WHERE n.IP IN (
  SELECT DISTINCT s.IP FROM news s
  GROUP BY s.IP
  HAVING count(s.KONTEN_ID)>1
)

Ich hab das mal analog auf die DB eines anderen Projekts losgelassen - das ist schweinemäßig langsam, weil MySQL für IN (SELECT) den Index auf IP nicht nutzt, aber funktioniert

Würdest du das Subselect "ausmultiplizieren", also die Ergebnismenge direkt als IN (123,345,567) schreiben, wäre das schnell.

Hallo,

ja, performant wäre gut, denn ich habe mein Beispiel eh schon stark vereinfacht, weil ich per UNION ALL 5 Tables auf einmal abfrage (was kein Problem ist), und deshalb die Ergebnisse schnell auf gut 5000 Netto-IPs kommen, die entsprechend gegengeprüft werden. Allein die normale Anfrage dauert schon 2-4 Sekunde bis zur Ausgabe, das jetzt auch noch mit der "nur IPs, die von zwei verschiedenen Usern benutzt wurden"-Klausel, würde sonst ewig dauern. Wenn die Anfrage ansonsten INSGESAMT 10-20 Sekunden dauern würde, wäre das aber in Ordnung.

jasocul · Verfasst: Fr 07.09.12 09:29

Ich habe im Moment leider keine Test-Möglichkeit und kann daher nur ein theoritisches SQL-Statement basteln. Da "IN" bei mySQL laut eurer Aussage keinen Index zieht, kämen evtl diese beiden Varianten in Frage:
Variante 1:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:

Select n2.konten_id, n2.ip
from (Select n1.ip, Count(n1.ip) cnt
      from news n1
      group by ip), news n2
where n1.ip = n2.ip
and cnt > 1

Variante 2:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:

Select n2.konten_id, n2.ip
from (Select n1.ip cnt
      from news n1
      group by n1.ip
      having Count(n1.ip) > 1), news n2
where n1.ip = n2.ip

Ich habe sowas schon mit Abfragen in Oracle gemacht und eine gute Performance gehabt.
Ob das auch bei mySQL funktioniert, müsstest du dann mal selbst ausprobieren.

TheUnknown · Beiträge: 334

Hallo Ihr beiden,

danke für die Antworten. Ich habe mir anders beholfen, und habe dabei eine sehr gute Geschwindigkeit erzielt. Es ist zwar nicht sehr elegant, aber ich mache es so, dass ich erst alle News abfrage, mit "SELECT ip, COUNT(DISTINCT(konten_id)) as menge_user". So erhalte ich alle IPs und der Menge an Userkonten, die die IPs mal benutzten. Nun gehe ich die Ergebnisse von 1 bis Ende durch und überspringe alle IPs, vo menge_user kleiner 2 ist.

Wie gesagt, nicht sehr elegant, aber es geht super schnell und ich hab das Thema endlich durch, weil das Resultat genau das ist, was ich wollte.

	Mitgliederliste
	Gruppen
	Das Team
	Richtlinien
	Synonyme