Geolocation Bezeichnungen der Regionen

[Matthias Apsel]

Hallo alle,

es gibt Anbieter, die versuchen den Standort des Besuchers zu ermitteln. Dass das mit unterschiedlichem Erfolg geschieht, wurde schon mehrfach in diesem Forum thematisiert.

geoplugin.com ist einer dieser Anbieter, der die Daten von MaxMind nutzt.

Wie bekomme ich heraus, welche Bezeichnungen die Regionen in Deutschland und Österreich tragen?

Bis demnächst
Matthias

[woodfighter]

Tach,

Wie bekomme ich heraus, welche Bezeichnungen die Regionen in Deutschland und Österreich tragen?

http://www.geonames.org/advanced-search.html?q=&country=DE&featureClass=A&continentCode=, Österreich analog

mfg
Woodfighter

[Matthias Apsel]

Hallo alle,

ich habe die Datenbank von maxmind auf meine Bedürfnisse angepasst. Sie besteht für Deutschland nur noch aus einer Tabelle mit ca. 100.000 Zeilen.

| begin_ip_num | end_ip_num | region | |----| |1591278080|1591278207|bw|

• Sind Indizes notwendig und sinnvoll oder eher kontraproduktiv?

Wenn ich nun das Herkunftsbundesland erraten möchte, gibt es mehrere Varianten.

SELECT region
FROM geoip
WHERE
1591278000 BETWEEN begin_ip_num AND end_ip_num

SELECT region
FROM geoip
WHERE
1591278000 >= begin_ip_num AND 1591278000 <= end_ip_num

• Mein Gefühl sagt mir, dass BETWEEN schneller sein wird. Gibt es da Erfahrungswerte?

Da es theoretisch nur maximal einen passenden Datensatz gibt, werde ich LIMIT 1 verwenden. Bricht MySQL tatsächlich nach dem ersten Fund ab? Die Spezifikation sagt nur „If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.“ Also Indizes verwenden?

• Gibt es ggf. eine andere Möglichkeit, das Durchsuchen der Tabelle nach dem ersten Fund abzubrechen?

---

Ob das Ganze vielleicht unsinnig ist, weil

• das (erstmalige) Laden der Seite wesentlich länger dauern wird (das wird zu testen sein)
• die Ergebnisse möglicherweise fehlerhaft sind

ist bekannt und soll hier nicht diskutiert werden

Bis demnächst
Matthias

[woodfighter]

Tach,

• Sind Indizes notwendig und sinnvoll

ja

oder eher kontraproduktiv?

fast nie, im Prinzip höchstens, wenn sehr viel geschrieben und selten gelesen wird oder die Indices schlecht gewählt sind (ich glaube ein Unique Index auf eine Boolean-Spalte ist dafür das Beispiel). Ich würde vermuten, dass ein kombinierter Index über beide Spalten hier die schnellste Variante ist, aber das verrät dir im Zweifelsfalle EXPLAIN.

SELECT region
FROM geoip
WHERE
1591278000 BETWEEN begin_ip_num AND end_ip_num

SELECT region
FROM geoip
WHERE
1591278000 >= begin_ip_num AND 1591278000 <= end_ip_num

• Mein Gefühl sagt mir, dass BETWEEN schneller sein wird. Gibt es da Erfahrungswerte?

Es würde mich wundern, wenn das tatsächlich einen Unterschied machen würde, das erste ist nur eine andere Schreibweise für das zweite.

Da es theoretisch nur maximal einen passenden Datensatz gibt, werde ich LIMIT 1 verwenden. Bricht MySQL tatsächlich nach dem ersten Fund ab?

Wenn du nicht noch sortierst, oder weitere Bedingungen hast, die es davon abhalten.

mfg
Woodfighter

[Christian Kruse]

Hallo woodfighter,

Da es theoretisch nur maximal einen passenden Datensatz gibt, werde ich LIMIT 1 verwenden. Bricht MySQL tatsächlich nach dem ersten Fund ab?

Wenn du nicht noch sortierst, oder weitere Bedingungen hast, die es davon abhalten.

Zu allgemein! Ein ORDER BY führt nicht zwangsläufig dazu, dass das komplette Result Set gebildet werden muss: CREATE INDEX foo ON bar(created_at); SELECT * FROM bar ORDER BY created_at LIMIT 1; hier kann das Sort/Limit über den Index abgebildet werden (diese Eigenschaft mache ich mir z.B. hier im Forum an mehreren Stellen zunutze).

LG,
CK

[Google weiß alles]

• die Ergebnisse möglicherweise fehlerhaft sind

Nein. Die sind weit überwiegend falsch.

Angeblich wohne ich in Dortmund ... ach nee: jetzt grad in Mühlheim, Hessen

[Christian Kruse]

Hallo Matthias,

• Sind Indizes notwendig und sinnvoll oder eher kontraproduktiv?

Das hängt von deinem Anwendungsfall ab. Wenn es so ist, wie ich rate (fast 100% lesender Zugriff), dann ist ein Index durchaus sinnvoll. Allgemeinplatz: ein Index macht bei großen Datenmengen Sinn, wenn regelmäßig lesend über diesen Pfad zugegriffen wird. Ausnahmen bestätigen die Regel.

• Mein Gefühl sagt mir, dass BETWEEN schneller sein wird. Gibt es da Erfahrungswerte?

Irrelevant. Selbst wenn es einen Unterschied im execution plan (hint EXPLAIN) gäbe, würde ihn der Optimizer weg optimieren. Schreib was lesbarer ist.

Da es theoretisch nur maximal einen passenden Datensatz gibt, werde ich LIMIT 1 verwenden. Bricht MySQL tatsächlich nach dem ersten Fund ab?

Das mit dem abbrechen ist so eine Sache. Das ist sehr stark abhängig von der Query. Bei so einer simplen Query dürfte das der Fall sein, ja. Bei anderen Queries muss erst ein Result Set gebildet werden, dass dann truncated wird. Deine Frage lässt sich so allgemein nicht beantworten. EXPLAIN bzw EXPLAIN EXTENDED wird dir hier mehr verraten. EXPLAIN ist eines der wichtigsten Tools im Bereich Datenbanken als Anwendungsentwickler! :-)

Die Spezifikation sagt nur „If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.“ Also Indizes verwenden?

Siehe oben ;-)

• Gibt es ggf. eine andere Möglichkeit, das Durchsuchen der Tabelle nach dem ersten Fund abzubrechen?

Nein. Ob „abgebrochen” werden kann, muss der Query Optimizer entscheiden. In diesem Fall würde ich mir allerdings keine Sorgen machen.

LG,
CK

[Matthias Apsel]

Hallo Christian Kruse,

• Sind Indizes notwendig und sinnvoll oder eher kontraproduktiv?

Das hängt von deinem Anwendungsfall ab. Wenn es so ist, wie ich rate (fast 100% lesender Zugriff), dann ist ein Index durchaus sinnvoll. Allgemeinplatz: ein Index macht bei großen Datenmengen Sinn, wenn regelmäßig lesend über diesen Pfad zugegriffen wird. Ausnahmen bestätigen die Regel.

Ausnahme ;-)

SELECT `land` 
FROM `ip-de` 
WHERE 1418465044 BETWEEN `anfang` AND `ende` 
LIMIT 1

ohne Index .01s mit Index .6s mit uniqe .4s.

Bis demnächst
Matthias

[Christian Kruse]

Hallo Matthias,

Ausnahme ;-)

SELECT `land` 
FROM `ip-de` 
WHERE 1418465044 BETWEEN `anfang` AND `ende` 
LIMIT 1

ohne Index .01s mit Index .6s mit uniqe .4s.

Und du hast das wie getestet? Ohne diese Angabe ist die Messung wertlos, sorry für die harten Worte :(

Datenbanken haben starke Caches. Wenn die Query ohne Index schon 1000 ausgeführt wurde, dann ist mit hoher Wahrscheinlichkeit die Query gecached. Um es also wirklich zu messen musst du einerseits dafür sorgen, dass der Cache leer ist (durch z.B. einen Reboot) und zweitens die Query öfter als einmal ausführen (z.B. 10.000 mal, mit BENCHMARK()).

LG,
CK

[Matthias Apsel]

Hallo Christian Kruse,

Und du hast das wie getestet?

In PHPMyAdmin.

Datenbanken haben starke Caches. Wenn die Query ohne Index schon 1000 ausgeführt wurde, dann ist mit hoher Wahrscheinlichkeit die Query gecached. Um es also wirklich zu messen musst du einerseits dafür sorgen, dass der Cache leer ist (durch z.B. einen Reboot) und zweitens die Query öfter als einmal ausführen (z.B. 10.000 mal, mit BENCHMARK()).

Das werd ich heute abend tun.

Bis demnächst
Matthias