nicht angemeldet
SQL Abfrage - Index setzen
Hallo, ich habe die folgende Abfrage:
SELECT count(id) AS anzahl
FROM results AS r1
WHERE r1.ip = ? AND (r1.scan_id) = (SELECT MAX(r2.scan_id) FROM results AS r2 WHERE r2.domain_id = r1.domain_id )
LIMIT 1;
Auf welche Spalten setze ich hier am besten einen Index?
Würde mich über Hilfe freuen!
Viele Grüße
Dennis
-
moin,
Auf welche Spalten setze ich hier am besten einen Index?
beim tuning muss man oftmals ausprobieren und schauen. die faktoren, ob ein index helfen kann oder eben nicht sind manigfaltig. oftmals erzielt man aber mit den üblichen verdächtigen schon eine gute wirkung. dies wären in deinem falle domain_id als einfacher index und ip und scan_id als zusammengesetzter index. da du das ergebnis eh auch einen datensatz beschränkst, kann man die abfrage auch umschreiben, indem man nach domain_id sortiert und die korrelierte unterabfrage wegläßt.
ps: deine klammern um r1.scan_id heraum sind überflüssig.
Ilja
-
da du das ergebnis eh auch einen datensatz beschränkst, kann man die abfrage auch umschreiben, indem man nach domain_id sortiert und die korrelierte unterabfrage wegläßt.
^^ Danke für deine Antwort. Kannst di mir diesen Teil aber bitte nochmal etwas ausführlicher erklären?
Dennis
-
Hallo Dennis,
da du das ergebnis eh auch einen datensatz beschränkst, kann man die abfrage auch umschreiben, indem man nach domain_id sortiert und die korrelierte unterabfrage wegläßt.
^^ Danke für deine Antwort. Kannst di mir diesen Teil aber bitte nochmal etwas ausführlicher erklären?
wieviele Datensätze erhältst Du durch den Einsatz von
LIMIT 1?Oha, genau einen.
Genau aus diesem Zweck kannst Du die teure korrelierte Unterabfrage gleich weglassen. Sie filtert Dir nur Datensätze aus, die Du eh' nicht benötigst.Du bist an den Einträgen interessiert, die einen bestimmten ip-Wert ausweisen und deren scan_id den höchsten Wert zu diesem ip-Wert aufweist:
SELECT COUNT(r.id), -- zähle die id-Werte r.scan_id -- und gib mir die Werte der scan_id FROM results r WHERE r.ip = <wert> -- zu einer bestimmten ip GROUP BY r.scan_id -- gruppiert nach den scan_id-Werten -- Anmerkung: manche DBMS wollen diese Spalte dann -- auch in der Spaltenliste haben ORDER BY r.scan_id DESC -- absteigend sortiert nach der scan_id, so dass die -- größte scan_id im ersten Datensatz steht LIMIT 1 -- und gib mir nur den ersten Datensatz zurück.Dieses Statement versagt, sobald Du mehr als einen Datensatz haben willst.
Freundliche Grüße
Vinzenz
-
Hallo Dennis,
da du das ergebnis eh auch einen datensatz beschränkst, kann man die abfrage auch umschreiben, indem man nach domain_id sortiert und die korrelierte unterabfrage wegläßt.
^^ Danke für deine Antwort. Kannst di mir diesen Teil aber bitte nochmal etwas ausführlicher erklären?
wieviele Datensätze erhältst Du durch den Einsatz von
LIMIT 1?Oha, genau einen.
Genau aus diesem Zweck kannst Du die teure korrelierte Unterabfrage gleich weglassen. Sie filtert Dir nur Datensätze aus, die Du eh' nicht benötigst.Du bist an den Einträgen interessiert, die einen bestimmten ip-Wert ausweisen und deren scan_id den höchsten Wert zu diesem ip-Wert aufweist:
SELECT
COUNT(r.id), -- zähle die id-Werte
r.scan_id -- und gib mir die Werte der scan_id
FROM
results r
WHERE
r.ip = <wert> -- zu einer bestimmten ip
GROUP BY
r.scan_id -- gruppiert nach den scan_id-Werten
-- Anmerkung: manche DBMS wollen diese Spalte dann
-- auch in der Spaltenliste haben
ORDER BY
r.scan_id DESC -- absteigend sortiert nach der scan_id, so dass die
-- größte scan_id im ersten Datensatz steht
LIMIT 1 -- und gib mir nur den ersten Datensatz zurück.> > Dieses Statement versagt, sobald Du mehr als einen Datensatz haben willst. > > > Freundliche Grüße > > Vinzenz Hallo Vinzenz, danke für deine Antwort. Das Problem bei meiner Abfrage ist folgendes: Ich habe eine Menge von Domains. Diese Domains haben 0,1 oder n IP Adressen zugeordnet. Die IP einer Domain kann wechseln. Gespeichert wird immer nur eine Änderung, d.h. die IP Adresse einer Domain ist anders. Die Abfrage muss nicht nur die Anzahl der Domains pro IP Adresse zählen, sondern auch noch schauen, sondern auch Prüfen, ob es zu einer Domain mit einer IP Adresse nicht einen aktuelleren Eintrag mit einer anderen IP Adresse und höherer scan\_id gibt. Ist das so, so muss der Eintrag mit der kleineren scan\_id ja wieder aus der Ergebnismenge entfernt werden. Die Zuordung von Domain zu IP stimmt dann nämlich nicht mehr, da die Domain eine neue IP hat. Gruß Dennis-
Hallo Dennis,
[TOFU] gelöscht. Bitte zitiere vernünftig.
Die Abfrage muss nicht nur die Anzahl der Domains pro IP Adresse zählen, sondern auch noch schauen, sondern auch Prüfen, ob es zu einer Domain mit einer IP Adresse nicht einen aktuelleren Eintrag mit einer anderen IP Adresse und höherer scan_id gibt. Ist das so, so muss der Eintrag mit der kleineren scan_id ja wieder aus der Ergebnismenge entfernt werden. Die Zuordung von Domain zu IP stimmt dann nämlich nicht mehr, da die Domain eine neue IP hat.
Ich kann aus Deinen Aussagen nichts entnehmen, was meinen einfacheren Vorschlag entkräften kann.
Würdest Du bitte ein paar konkrete Beispieldaten (gern mit example.com und Co. maskiert) zeigen und das gewünschte Resultat Deiner Abfrage - mit allen Spalten, die Du benötigst. Sonst artet das hier in ein Ratespiel für die potentiellen Helfer aus.
Freundliche Grüße
Vinzenz
-
-
-
moin,
^^ Danke für deine Antwort. Kannst di mir diesen Teil aber bitte nochmal etwas ausführlicher erklären?
dazu muss ich ein wenig ausholen. oftmals will man abfragen machen, gib mir das höchste gehalt oder den schnellsten läufer. dabei gibt es eine problematik, die oftmals vergessen wird. die meisten gehen davon aus, dass sie dann immer einen datensatz zurück bekommen. das ist aber nicht ganz richtig, es können durchaus mehrere sein, nämlich dann, wenn zwei oder mehr angestellte das gleiche gehalt bekommen, was auch noch das höchste in der firma ist. oftmals will man aber trotzdem ganz bewußt nur einen datensatz haben.
mysql hate eine schöne funktionalität, wenn man genau einen datensatz zurück bekommen will, egal ob es mehrere max werte gibt oder nicht, nämlich indem man vorher nach der spalte sortiert und dann limitiert. leider wird das aber ofmtals falsch eingesetzt und noch viel schlimmer, im gegensatz zu einer unterabfrage fällt es häufig gar nicht auf, dass es mehrere datensätze mit den gleichen maxwet gibt, weil man es ja eh auf einen datensatz limitiert.
in deinem fall ist es noch ein wenig komplizierter, da deine unterabfrage noch korreliert und mir noch nicht ganz klar ist, was du überhaupt erreichen willst. eventuell hast du auch ein problem drin, was du noch nicht siehst. aber vielleicht ist auch alles richtig so. wäre halt gut, wenn du mal genau sagen könntest, was die abfrage bewirken soll mit beispieldatensätzen.
Ilja
-
-