Hilfe, ich habe ein Zeitproblem
Heiko Warnken
Um es vorweg zu nehmen, ich mache nach längerer Zeit mal wieder
Erfahrungen mit PHP und MySQL.
ich glaube, ich habe ein Geschwindigkeitsproblem im Zusammenspiel mit
PHP 5 und MySQL 5. Hier findet alles auf einem lokalen Server im LAN
statt, der Webserver ist mit XAMPP (neueste Version) realisiert.
Es geht um Folgendes:
Ich habe mir von http://www.geoiptool.com/de/webtools/ die Datenbanken
für Country und City heruntergeladen und in meine Datenbankstruktur
importiert. Die Datenbankstruktur ist so aufgebaut, wie es auf der o. g.
Webseite beschrieben ist.
Es gibt in der Datenbank 3 Tabellen:
Tabelle 1: Country
In dieser Tabelle befinden sich IP-Adressen-Bereiche und das
dazugehörige Land
Also z. B.:
vonip 94.199.200.1 - vchar(16)
bisip: 94.199.205.199 - vchar(16)
Country: Germany vchar(32)
Countrycode: DE - Text(2)
Tabelle 2: blocks
In dieser Tabelle befinden sich ebenfalls die IP-Adressen, allerdings im
Integer-Format sowie die entsprechend dazugehörige Location-ID
vonIpNum: 1234567890 - Int(10), unsigned
bisIpNum: 2345678901 - Int(10), unsigned
locationID: 76193 Int(6), unsigned
Tabelle 3: Location
Hier wird die aus Tabelle 2 ermittelte Location-ID einem Ort zugeordnet
locationID: 76193 Int(6), unsigned
Country: DE - Text(2)
Region: 16 - Text(2)
City: Berlin - vchar(32)
(Region = Bundesland von 01 bis 16 als Kennziffer für 16 Bundesländer)
Um nun herauszufinden, zu welchem Ort eine IP-Adresse zugeordnet wird,
gehe ich schematisch wie folgt vor:
1. Ermitteln der IP-Adresse
$ip = $_SERVER['REMOTE_ADDR']
2. Aus welchem Land?
$SQL = "SELECT landname, landcode FROM country WHERE INET_ATON(vonip) <=
'$ip' AND INET_ATON(bisipnum) >= '$ip'"
3. Ländername und Code speichern
4. Aus welcher Stadt?
$ip = sprintf("%u", ip2long($ip))
$SQL = "SELECT locid FROM blocks WHERE startipnum >= '$ip' AND endipnum
<= '$ip'"
$SQL = "SELECT city, region FROM location WHERE locid = '$locid'"
(Diese beiden Anweisungen brauchen ziemlich lange Zeit)
5. Speichern der herausgefundenen Daten in ein Array
Da die Schritte 1 - 5 innerhalb einer Function stattfinden, wird ein
Array an das aufrufende Script zurückgegeben.
$info = Get_Location_Data($ip)
Den Inhalt von $info[] kann ich dann ganz normal ausgeben lassen, z. B.
echo $info["Stadt"]
Soweit alles gar kein Problem, wenn das Ganze nicht viel zu lange dauern
würde, denn die o. g. angegebenen SQL-Abfragen dauern über 1,5 Sekunden.
Da das aufrufende Script aber eine maximale Laufzeit von 60 Sekunden
hat, kann sich jeder ausrechnen, dass ich auf die Art nicht mehr als
max. 40 IP-Adressen überprüfen kann. Die ganze Function
Get_Location_Data($ip) benötigt 1,83 Sekunden.
Wenn ich jetzt die entsprechende Function am Ende Anfüge, wird sich der
Eine oder Andere von Euch bestimmt sagen, dass die Function total
idiotisch programmiert ist, was wohl auch stimmt. Mir geht's allerdings
hier in der Hauptsache um die SQL-Abfragen und um deren
Geschwindigkeitsverbesserung.
Wenn mir dazu jemand einen Tipp geben kann, wäre ich ihm sehr dankbar,
denn ich möchte in Kürze die Auswertung der IP-Adressen mit bis zu 500
Stück durchführen können, ohne dass das Script wegen Zeitüberschreitung
abbricht.
Danke und Gruß
Heiko
So, und jetzt noch die entsprechende Function:
<?PHP
function Get_Location_Data($ip = "")
{
// Diese Funktion gibt in einem Array einige geografische Angaben zurück
// Location_ID : Identifikationsnummer des Ortes
// Land : ausgeschriebener Ländername
// Landcode : internationale Abkürzung des Landes
// Stadt : ausgeschriebener Name der Stadt
// Region : Nummer des Bundeslandes
// Regionname : Name des Bundeslandes
//Latitude : Breitengrad
// Longitude : Längengrad
//
$ret = array();
$bland = array("01" => "Baden-Württemberg", "02" => "Bayern", "03" =>
"Bremen", "04" => "Hamburg", "05" => "Hessen", "06" => "Niedersachsen",
"07" => "Nordrhein-Westfalen", "08" => "Rheinland-Pfalz", "09" =>
"Saarland", "10" => "Schleswig-Holstein", "11" => "Brandenburg", "12" =>
"Mecklenburg-Vorpommern", "13" => "Sachsen", "14" => "Thüringen", "15"
=> "Sachsen-Anhalt", "16" => "Berlin");
if ($ip == "")
{
$ip = $_SERVER['REMOTE_ADDR'];
}
$ip= sprintf("%u", ip2long($ip));
$sql = "SELECT landcode, landname FROM country WHERE INET_ATON(vonip) <=
'" . $ip . "' AND INET_ATON(bisip) >= '" . $ip . "'";
verbinden();
$erg=mysql_query($sql);
if (!$erg){return "---";}
$land = mysql_fetch_object($erg);
$ret["Land"] = $land->landname;
$ret["Landcode"] = $land->landcode;
if (mysql_num_rows($erg) == 0)
{
$ret["Land"] = "unbekannt";
$ret["Landcode"] = "---";
}
$sql = "SELECT HIGH_PRIORITY locid FROM blocks WHERE startipnum <= '$ip'
AND endipnum >= '$ip'";
// verbinden();
$erg1=mysql_query($sql);
$l1 = mysql_fetch_object($erg1);
$locid = $l1->locid;
$ret["Location_ID"] = $locid;
$sql = "SELECT HIGH_PRIORITY region, city, latitude, longitude FROM
location WHERE locid = '$locid'";
// verbinden();
$erg2 = mysql_query($sql);
$city = mysql_fetch_object($erg2);
if ($city->city == "")
{
$ret["Stadt"] = "unbekannt";
}
else
{
$ret["Stadt"] = $city->city;
}
$ret["Region"] = $city->region;
$ret["Regionname"] = $bland["$city->region" ];
$ret["Latitude"] = $city->latitude;
$ret["Longitude"] = $city->longitude;
return $ret;
}
?>
Michael Fesser
>ich glaube, ich habe ein Geschwindigkeitsproblem im Zusammenspiel mit
>PHP 5 und MySQL 5. Hier findet alles auf einem lokalen Server im LAN
>statt, der Webserver ist mit XAMPP (neueste Version) realisiert.
>Es geht um Folgendes:
>Ich habe mir von http://www.geoiptool.com/de/webtools/ die Datenbanken
>für Country und City heruntergeladen und in meine Datenbankstruktur
>importiert. Die Datenbankstruktur ist so aufgebaut, wie es auf der o. g.
>Webseite beschrieben ist.
Wo? Ich sehe da weder eine Beschreibung noch was zum Runterladen.
Lediglich ein JavaScript und ein Beispiel.
>Es gibt in der Datenbank 3 Tabellen:
>[…]
Eins vorweg: Es wäre sinvoller, in der MySQL-Gruppe nachzufragen, denn
das ist primär erstmal ein SQL-Problem und nicht direkt PHP-bezogen.
Daher nur ein paar allgemeine Stichworte:
1) Sind die Tabellen korrekt und sinnvoll indiziert?
2) Dauern Deine Abfragen auf der Kommandozeile genauso lange?
3) Was sagt EXPLAIN?
>Um nun herauszufinden, zu welchem Ort eine IP-Adresse zugeordnet wird,
>gehe ich schematisch wie folgt vor:
>
>1. Ermitteln der IP-Adresse
>$ip = $_SERVER['REMOTE_ADDR']
>
>2. Aus welchem Land?
>$SQL = "SELECT landname, landcode FROM country WHERE INET_ATON(vonip) <=
>'$ip' AND INET_ATON(bisipnum) >= '$ip'"
Das ist schonmal ungünstig, da bei jeder einzelnen Abfrage Unmengen von
INET-ATON()-Aufrufen nötig sind. Diese Information, d.h. die IP-Adresse
bzw. der Bereich im Long-Format, sollte auch schon direkt in der Tabelle
gespeichert sein, um Konvertierungen zu vermeiden. Ein bißchen Redundanz
schadet nicht.
>3. Ländername und Code speichern
>4. Aus welcher Stadt?
>$ip = sprintf("%u", ip2long($ip))
>$SQL = "SELECT locid FROM blocks WHERE startipnum >= '$ip' AND endipnum
><= '$ip'"
>$SQL = "SELECT city, region FROM location WHERE locid = '$locid'"
Das läßt sich zu einer Abfrage zusammenfassen. Ohnehin sehe ich nicht
ganz den Sinn in den beiden Tabellen Country und Blocks. IMHO ließen
sich die beiden auch irgendwie besser kombinieren. Aber ich habe die
Tabellen nicht hier, kann mich da also nicht weiter reindenken.
>(Diese beiden Anweisungen brauchen ziemlich lange Zeit)
Sollten sie aber natürlich nicht.
>5. Speichern der herausgefundenen Daten in ein Array
>
>Da die Schritte 1 - 5 innerhalb einer Function stattfinden, wird ein
>Array an das aufrufende Script zurückgegeben.
>$info = Get_Location_Data($ip)
>Den Inhalt von $info[] kann ich dann ganz normal ausgeben lassen, z. B.
>echo $info["Stadt"]
>
>Soweit alles gar kein Problem, wenn das Ganze nicht viel zu lange dauern
>würde, denn die o. g. angegebenen SQL-Abfragen dauern über 1,5 Sekunden.
>Da das aufrufende Script aber eine maximale Laufzeit von 60 Sekunden
>hat, kann sich jeder ausrechnen, dass ich auf die Art nicht mehr als
>max. 40 IP-Adressen überprüfen kann.
Wenn Du mehrere IPs prüfen willst, dann baue nach Möglichkeit Deine
Abfragen und Deine Funktion so um, daß Du ein Array mit IPs übergibst
und mit einer einzigen Abfrage alles in einem Rutsch prüfst. Du willst
nicht unbedingt 40 einzelne, noch dazu gleichartige, Abfrage raushauen.
>Die ganze Function
>Get_Location_Data($ip) benötigt 1,83 Sekunden.
>Wenn ich jetzt die entsprechende Function am Ende Anfüge, wird sich der
>Eine oder Andere von Euch bestimmt sagen, dass die Function total
>idiotisch programmiert ist, was wohl auch stimmt. Mir geht's allerdings
>hier in der Hauptsache um die SQL-Abfragen und um deren
>Geschwindigkeitsverbesserung.
Wie oben gesagt: EXPLAIN wäre der erste Ansatzpunkt, dann die MySQL-
Gruppe. Wenn es schon bei den SQL-Abfragen klemmt, dann kannst Du mit
PHP auch nichts mehr reißen.
Micha
Thomas Hochstein
> Ich habe mir von http://www.geoiptool.com/de/webtools/ die Datenbanken
> für Country und City heruntergeladen und in meine Datenbankstruktur
> importiert. Die Datenbankstruktur ist so aufgebaut, wie es auf der o. g.
> Webseite beschrieben ist.
Unter dem genannten Link sehe ich keine Beschreibung einer
Datenbankstruktur.
> Es gibt in der Datenbank 3 Tabellen:
[...]
Wie sieht's mit Indexen aus?
> 2. Aus welchem Land?
> $SQL = "SELECT landname, landcode FROM country WHERE INET_ATON(vonip) <=
> '$ip' AND INET_ATON(bisipnum) >= '$ip'"
> 3. Ländername und Code speichern
> 4. Aus welcher Stadt?
> $ip = sprintf("%u", ip2long($ip))
> $SQL = "SELECT locid FROM blocks WHERE startipnum >= '$ip' AND endipnum
> <= '$ip'"
> $SQL = "SELECT city, region FROM location WHERE locid = '$locid'"
> (Diese beiden Anweisungen brauchen ziemlich lange Zeit)
Was sagt EXPLAIN?
-thh
Heiko Warnken
erstmal vielen Dank für Eure Antworten.
Zum besseren Verständnis habe ich die angesprochenen Datenbankdateien
(im CSV-Format), die kostenlos heruntergeladen werden können, mal zum
Download unter
http://momentmal.eu/download/GeoIP-COM-1.3.zip
und
http://momentmal.eu/download/GeoLiteCity_20110101.zip
bereitgestellt. Die erste Datei enthält die Tabelle für die IP-Adressen
und der dazugehörenden Länder, die zweite Datei enthält die Tabellen
"blocks" und "Location", die die Indizies für die einzelnen Orte
enthalten sowie zu den entsprechenden Indizes die entsprechenden
Ortsbezeichnungen.
In allen Tabellen sind die Felder, die ausschließlich numerische Daten
enthalten, als INT(10), unsigned definiert. Die ersten beiden Spalten in
der Tabelle Country enthält die IP-Adressen im Klartext, also x.x.x.x
und sind dementsprechend als Text mit einer festen Länge von 16 Zeichen
definiert.
Wäre schön, wenn mir doch noch jemand helfen könnte.
Gruß
Heiko
Stefan Froehlich
> Eins vorweg: Es wäre sinvoller, in der MySQL-Gruppe nachzufragen, denn
> das ist primär erstmal ein SQL-Problem und nicht direkt PHP-bezogen.
Und wenn er dort versehentlich auch nur 3 Zeichen vom PHP-Code rund um
seine Abfrage herum stehen laesst, schicken sie ihn wieder zurueck hierher,
weil es ja "ein PHP-Problem ist"... (wiewohl Du natuerlich recht hast, in
der mysql-Gruppe sind die Leute bloss ein wenig arg ueberallergisch).
Servus,
Stefan
--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich
Offizieller Erstbesucher(TM) von mmeike
Stefan - denn kalte Liebe blubbert nimmermehr.
(Sloganizer)
Gregor Kofler
> Hallo,
>
> erstmal vielen Dank für Eure Antworten.
>
> Zum besseren Verständnis habe ich die angesprochenen Datenbankdateien
> (im CSV-Format), die kostenlos heruntergeladen werden können, mal zum
> Download unter
>
> http://momentmal.eu/download/GeoIP-COM-1.3.zip
> und
> http://momentmal.eu/download/GeoLiteCity_20110101.zip
>
> bereitgestellt. Die erste Datei enthält die Tabelle für die IP-Adressen
> und der dazugehörenden Länder, die zweite Datei enthält die Tabellen
> "blocks" und "Location", die die Indizies für die einzelnen Orte
> enthalten sowie zu den entsprechenden Indizes die entsprechenden
> Ortsbezeichnungen.
Ohne mir das runterladen zu wollen: Die konkreten Daten sind reichlich
uninteressant. Wie schaut es aus mit Indizes? Welche Storage Engine wird
verwendet? Wie die Vorposter schon sagten: Interessant ist die DDL der
Tabellen und was EXPLAIN bei den Queries liefert. Und damit bist du dann
in d.c.d.mysql besser aufgehoben.
[Fullquote unten entsorgt]
Gregor
Gregor Kofler
> ...in d.c.d.mysql besser aufgehoben.
Hab es gerade gesehen: Da wurdest du eh schon geholfen.
Gregor