Denkfehler bei SQL-Abfrage?

[Klaus1]

Hallo,

ich möchte mit einer SQL-Abfrage alle Protokolleinträge älter als x Tage finden. Es wird hier jede Anmeldung eines Benutzers protokolliert.

Die Protokolltabelle hat folgende Felder:

idnr (int autoincrement)
username (varchar)
logindate (varchar)
logoutdate (varchar)
dauer (varchar)
status (char) A=aktiv

Das das sinnvollerweise Datumsfelder sein sollten und nicht Varchar ist mir bewusst, aber jetzt im System nicht mehr so leicht zu ändern.

Meine SQL-Abfrage sieht so aus:

select * 
from vpnprotokoll 
where logindate <> '' AND logindate < '2021-04-01' 
AND status = 'A' 
AND logindate IN (select MAX(logindate) 
                  from vpnprotokoll group by username)
order by username

Irgendwo scheint da ein Denkfehler zu sein, denn die Abfrage liefert (neuerdings) auch jemanden, der sich täglich anmeldet und auch aktuelle Einträge in der Tabelle stehen hat (z.B. von heute)

Ein

select MAX(logindate)
from vpnprotokoll
where username = 'problemkind' group by username`

zeigt korrekt den letzten Eintrag (von heute) des Benutzers an.

Kann mir jemand auf die Sprünge helfen?

LG Klaus

Edit Rolf B: Umbrüche in SQLs eingefügt, Column-Liste in ~~~ eingeschlossen

[Klaus1]

Hallo nochmal,

eine mögliche Lösung habe ich vielleicht:

SELECT * 
FROM vpnprotokoll AS vpn1
WHERE vpn1.logindate <> '' AND vpn1.logindate < '2021-04-01'
  AND vpn1.status = 'A'
  AND vpn1.logindate = (SELECT MAX(logindate) 
                        FROM vpnprotokoll AS vpn2
                        WHERE vpn1.username = vpn2.username)
ORDER BY vpn1.username;   

Diese Abfrage dauert aber schon mehr als 6 Sekunden 😟

Es gibt einen Index, sowohl auf username als auch auf logindate.

LG Klaus

Edit Rolf B: SQL formatiert

[Felix Riesterer]

Lieber Klaus1,

Diese Abfrage dauert aber schon mehr als 6 Sekunden 😟

Du willst, dass Deine DB mit Strings hantiert, anstatt dass sie Datumswerte vergleichen darf. Dann lebe damit.

Liebe Grüße

Felix Riesterer

[Rolf B]

Hallo Felix,

mein Minus. Ob String oder Date hat keinen signifikanten Einfluss auf die Performance.

Rolf

[dedlfix]

Tach!

Diese Abfrage dauert aber schon mehr als 6 Sekunden 😟

Du willst, dass Deine DB mit Strings hantiert, anstatt dass sie Datumswerte vergleichen darf. Dann lebe damit.

Die Aussage ist nicht weiter sinnvoll. Eine Datenbank kann auch Stringsvergleiche effizient durchführen. Sonst würden Abfragen nach Text-Inhalten per se langsam sein. Langsamer als Zahlenvergleiche sicherlich (Unicode mit Besonderheiten nochmal), aber nicht in solchen Größenordnungen.

dedlfix.

[Rolf B]

Hallo Klaus1,

ich möchte mit einer SQL-Abfrage alle Protokolleinträge älter als x Tage finden.

Das tut deine Abfrage definitiv nicht. Ich würde das Ergebnis so formulieren: Du willst herausfinden, wann die letzte Anmeldung derjenigen User war, die sich seit dem 1. April nicht mehr angemeldet haben.

Der Denkfehler der Ursprungsquery wude bereits genannt.

Deine modifizierte Query macht es richtig: sie betrachtet nur das jüngste Login-Datum des Users, dem der aktuelle Satz gehört.

Es gibt einen Index, sowohl auf username als auch auf logindate.

Bitte formuliere das genauer. Wieviele Indexe gibt es, welcher Art sind sie und in welchem Index befinden sich welche Key-Columns?

Rolf

[Klaus1]

Hallo,

Es gibt insgesamt drei Indexe, alle Typ BTREE, alle Kollation A und selbe Kardinalität: idnr unique primary username logindate

LG Klaus

[Rolf B]

Hallo Klaus1,

ich habe keinen hinreichend großen Datenbestand, um einen performancerelevanten Test zu machen. Es könnte aber sinnvoll sein, deine Query auf den Kopf zu stellen, so dass nicht für jeden User eine MAX Suche gemacht werden muss.

Kennst Du HAVING?

SELECT vp2.*
FROM (SELECT username, MAX(logindate) as maxlogin
      FROM vpnprotokoll 
      GROUP BY username
      HAVING MAX(logindate) < '2021-04-01') vp1
    JOIN
      vpnprotokoll vp2
    ON vp1.username = vp2.username AND vp1.maxlogin = vp2.logindate
WHERE vp2.status = 'A'
ORDER BY vp2.username

Diese Query ermittelt zunächst für alle User ihr maximales Login-Datum. Mit HAVING wird das auf alle User gefiltert, deren letzter Login vor dem 1.4.21 lag. Wenn Du einen Index auf (username, logindate) oder auch (logindate, username) hast, sollte sich dieser Teil der Query durch einen Index Scan erledigen lassen.

Und erst, wenn das getan ist, werden die eigentlichen Datensätze hinzugemischt. Was pro Satz durch einen Index Seek gemacht werden kann, es sei denn, deine SQL Engine meint, sie könnte das durch einen Tablescan effizienter lösen.

Da hilft dann nur ein EXPLAIN, um das festzustellen, und ggf. Index Hints in der äußeren Query, um einen Index zu erzwingen (mit erneuter Performancemessung; wenn der Query Optimizer meint, ein Tablescan wäre besser, hat er oft recht).

Rolf

[Klaus1]

Hallo Rolf,

vielen lieben Dank für Dein Beispiel. Die Abfrage funktioniert super und dauert unter einer Sekunde.

Ich werde mich aber wohl noch mit dem Index-Thema beschäftigen müssen, dann bekomme ich sicher die Abfrage noch schneller hin. Bisher habe ich tatsächlich noch keinen kombinierten Index erstellt. 🤫

LG Klaus

[Rolf B]

Hallo Klaus1,

getrennte Indexe für logindate und username helfen bei einer Abfrage wie

SELECT username, MAX(logindate) as maxlogin 
FROM vpnprotokoll 
GROUP BY username

leider gar nichts.

Der Server könnte über den username-Index die Logtabelle indexsequenziell lesen (d.h. die Rows der Table in Indexreihenfolge verarbeiten), das ist aber nur sinnvoll, wenn es wenige Sätze sind. Andernfalls bedeutet indexsequenzielles Lesen einer Table nur, dass er kreuz und quer durch den Tablespace springen muss, und der Lesekopf der Festplatte springt mit, bis das Schmieröl kocht.

Ein kombinierter Index ermöglicht Abfragen über einen Index Scan. Zusätzliche Indexspalten bedeuten natürlich auch, dass eine Indexpage weniger Rows enthält und der Index damit größer wird. Das sequenzielle Lesen eines BTree ist aber sehr effizient, das Format ist genau dafür gemacht.

Das ist immer der Balanceakt:

• Zu wenig Indexe oder zu wenig Spalten in den Indexen - die Querys werden langsam
• Zu viele Indexe - die INSERTS werden langsam (UPDATEs auf Indexspalten natürlich auch)

Da hilft nur:

• Katalogisieren aller SQL Operationen auf einer Table
• Für jede Operation aufschreiben, wie oft sie (mutmaßlich) ausgeführt wird
• Für jede Operation einen EXPLAIN machen
• Gucken, welche Indexe überhaupt von der Engine genutzt werden
• Für jede Operation im EXPLAIN prüfen, wo Table Scans stattfinden und überlegen, ob hier ein Index helfen kann
• Unnötige Indexe vermeiden
  • ein Index auf Spalte A ist sinnlos, wenn es einen weiteren Index über die Spalten (A,B) gibt
  • ein Index, der eine seltene Query beschleunigt, ist sinnlos, wenn dadurch eine häufige Query ausgebremst wird
• Wenn es knüppeldick kommt: Trennung von operativen und dispositiven Daten. Für Auswertungen (dispositiv) braucht man oft andere Indexe als für den laufenden Betrieb (operativ), und Auswertungen müssen nicht zwingend auf dem aktuellsten Stand gefahren werden. Man kann die operative DB über Synchonisierverfahren in eine zweite, dispositive DB doppeln - z.B. einmal in der Nacht. Die dispositive DB kann andere Indexe haben, die den operativen Betrieb bremsen würden, aber für performante Auswertungen taugen. Und ein 10 Minuten SQL auf der dispositiven DB ist lange nicht so schmerzhaft wie auf der operativen DB.

Rolf

[KLaus1]

Hallo Rolf,

nochmals Danke für Erläuterungen. Das hilft mir sehr weiter. Mit Performance-Messungen habe ich mich bisher nicht befasst (bzw. befassen müssen), weil die Datenauswertungen bisher immer so schnell gingen (im Zehntelsekunden-Bereich) oder es dispositiv keine Rolle gespielt hat.

LG Klaus

[dedlfix]

Tach!

Zeilenumbrüche wären sehr zuvorkommend gewesen, weil man dann die Query besser erfassen und sich das Querscrollen größtenteils sparen kann.

select * from vpnprotokoll 
where 
  logindate <> '' AND
  logindate < '2021-04-01' AND
  status = 'A' AND
  logindate IN (
    select MAX(logindate) from vpnprotokoll group by username)
order by username

Irgendwo scheint da ein Denkfehler zu sein, denn die Abfrage liefert (neuerdings) auch jemanden, der sich täglich anmeldet und auch aktuelle Einträge in der Tabelle stehen hat (z.B. von heute)

Die Subquery ermittelt die jeweils neuesten Login-Datümer aller User. Und diese mixt du dann mit denen der einzelnen User. Wenn du die Query nie verändert hast, war sie schon immer seltsam/falsch.

Vielleicht willst du eine Subquery, die sich auf den Nutzer der Hauptquery bezieht, also eine Correlated Subquery.

Ein

select MAX(logindate) from vpnprotokoll
where username = 'problemkind'
group by username

zeigt korrekt den letzten Eintrag (von heute) des Benutzers an.

Ein GroupBy ist in dem Fall aber sinnlos, weil die Datenmenge ja sowieso schon auf nur einen Nutzer begrenzt ist.

dedlfix.

[encoder]

Ich habe ein Beispiel für das was dedlfix erwähnt.

die Abfrage liefert (neuerdings) auch jemanden, der sich täglich anmeldet und auch aktuelle Einträge in der Tabelle stehen hat (z.B. von heute)

Benutzer A meldet sich täglich an.
Benutzer X meldet sich selten an.

A - heute
A - gestern
A - sonst wann
A - 1.3.
X - 1.3.

Die Subquery findet das jeweils letzte logindate aller Benutzer. Das wäre
A - heute
X - 1.3.

Die Hauptquery findet nun für A einen Eintrag mit 1.3. und stellt fest dass dieser Eintrag in der Subquery vorkommt. Zwar nicht für A sondern für X, aber das wird nicht gefiltert.