✔ SQL PLZ Entfernung User sortieren

[Dimenson]

Hallo,

wie immer stehe ich vor einem Problem. Ich programmiere ein Portal wo sich User anmelden können und nach Usern suchen können.
User können auch sehen wie weit andere User von ihm entfernt sind. Dafür habe ich das hier verwendet:
http://www.tutorials.de/content/1314-php-klasse-fuer-ortsbezogene-umkreissuche.html

Nun bin ich bei der "Suche" auf die Problematik bei der Sortierung gestoßen. Ich wollte gern, dass man User nach Entfernung sortieren kann unabhängig nach der Umkreissuche. Und das bei allen Usern.

Momentan habe ich folgende Tabellen:

geodb_locations
- id, country, plz, name, lat, lon

wi_user
- username, plz ....


Jetzt weiß ich leider nicht, wie es nun schaffe, User nach Entfernung zu sortieren unabhängig von der Umkreissuche.
Das erste was mir in den Sinn kam, war das ich nun eine 3. Tabelle einsetze:

wi_user_geo_search
- plz1
- plz2
- distance

Und so mir die Distanzen in die DB abspeichere. Das Ding ist halt, das es 21.464 Datensätze an PLZs gibt, das heißt, ich würde dafür eine betrachliche Menge an Datensätzen abspeichern. Dürften so um die 500 Millionen sein ^^.

Ich hoffe ihr versteht was ich vorhabe und wie die Problematik gemeint ist.


So sieht zum Beispiel meine SQL-Abfrage für die Umkreissuche aus:

SELECT 
   wi_user_geo_search.*,
   wi_user.* 
FROM 
   wi_user_geo_search 
JOIN 
   wi_user 
ON 
   wi_user.user_plz=IF(wi_user_geo_search.plz1 <> '72202', wi_user_geo_search.plz1, wi_user_geo_search.plz2) 
WHERE 
   (wi_user.user_relat='0' OR wi_user.user_relat='') 
AND 
   (wi_user_geo_search.plz1='72202' OR wi_user_geo_search.plz2='72202') 
AND
    wi_user_geo_search.distance <=10 
AND 
   NOT wi_user.id='18' 
AND 
   NOT wi_user.id=0 
GROUP BY 
   IF(wi_user_geo_search.plz1 <> '72202', wi_user_geo_search.plz1, wi_user_geo_search.plz2) ORDER BY user_register_date DESC LIMIT 0,20

Mit dieser SQL Abfrage, hole ich mir gleichzeitig die Userdaten und den Datensatz aus wi_user_geo_search wo die Distanzen der einzelnen PLZ drin stehen. Und mit "wi_user_geo_search.distance <=10" gebe ich die Distance die ich haben will an, hier 10km.

Zuvor habe ich eine Abfrage eingebaut, die prüft ob der Datensatz mit der 1. PLZ und der 2.PLZ in der "wi_user_geo_search" vorhanden ist oder nicht, dementsprechend legt er es an oder nicht.

Ich weiß momentan nicht ob ich generell auf den richtigen Weg bin, momentan bin ich mit der Lösung eigentlich ein bißchen unzufrieden. Und ich bin mir sicher, das es noch evlt. noch eine elegantere Lösung als meine gibt. Bloß momentan weiß ich nicht weiter. Ich hoffe ihr habt mir einen Rat, Denkanstoß etc.

Danke

Gruß

 

[ComFreek]

Und so mir die Distanzen in die DB abspeichere. Das Ding ist halt, das es 21.464 Datensätze an PLZs gibt, das heißt, ich würde dafür eine betrachliche Menge an Datensätzen abspeichern. Dürften so um die 500 Millionen sein ^^.

Dies ist aber dann nur eine Lookup-Table, sodass es - bei richtiger Optimierung - nicht große Performancehits geben sollte.

Ansonten könntest du die Distanz auch dynamisch im Query berechnen, allerdings würde dies viel eher auf die Ausführungs-Geschwindigkeit gehen.

 

[Dimenson]

Ja, ich denke ich komme um die Tabelle nicht rum. Allein schon wegen der Performance.
Ich schätze mal, wenn ich nun alle Daten ausrechnen lassen und abspeichere, komme ich ca. auf 15-18Gb und auf 460Millionen Datensätze.

Was heißt denn bei dir "bei richtiger Optimierung" ? Worauf ich geachtet habe, sind welche Datentypen ich hier verwende, hauptsächlich Integer.

id int(14)
plz1 int(5)
plz2 int(5)
distance int(5)

 

[ComFreek]

Was heißt denn bei dir "bei richtiger Optimierung" ? Worauf ich geachtet habe, sind welche Datentypen ich hier verwende, hauptsächlich Integer.

Laut MySQL-Doku sollte man darauf achten, dass man den kleinstmöglichen Int-Typ nimmt. Eine Tabelle gibt es hier.

Ich würde folgendes also vorschlagen:

CREATE TABLE zip_distances (
  plz1 SMALLINT UNSIGNED NOT NULL,
  plz2 SMALLINT UNSIGNED NOT NULL,
  distance SMALLINT UNSIGNED,
  
  PRIMARY KEY(plz1, plz2)
) ENGINE=MyIsam;

CREATE INDEX plz1_index ON zip_distances(plz1);
CREATE INDEX plz2_index ON zip_distances(plz2);

Ich hätte eigentlich lieber auf ARCHIVE als Engine getippt, allerdings werden dann ein zwei-spaltiger Primarykey und Indizes nicht unterstützt.

Wenn man davon ausgeht, dass ein SMALLINT eine Größte von 2 Bytes besitzt, dann kommt man auf mindestens 6 Bytes pro Datensatz (zuzüglich Metainformationen, etc. seitens MySQL).

6 Bytes * 460 681 832 Datensätze = ~2,57 Gigabyte
Wie kommst du denn auf 15 - 18Gb oder habe ich einen großen Denkfehler gerade?

 

[Dimenson]

Moin,

Smallint geht wohl nur bis 65535, muss leider auf Mediumint zurückgreifen.
Ist ENGINE=MyIsam nicht veraltet? Ich meine ich habe mal was von einer Speichergrenze von 4gb gelesen, aber hinge wohl vom Dateisystem ab.

Naja meine ich zumindest.

Aber mit deiner Variante konnte ich 6-7 Bytes wohl erzielen pro Datensatz, das ist natürlich enorm weniger.

 

[alxy]

PLZs sind auch keine integers
Zumindest normalerweise nicht, wie man da bei so großen Datenmengen mit umgeht, weiß ich leider nicht.

 

[ComFreek]

PLZs sind auch keine integers
Zumindest normalerweise nicht, wie man da bei so großen Datenmengen mit umgeht, weiß ich leider nicht.

In der Tat. Ich würde eigentlich mit CHAR(5) (oder 10 bei kanadischen PLZs) hantieren, allerdings haben 5 Zeichen eine minimale Speichergröße von 5 Bytes (bei UTF-8 sind es 15 Bytes).
Wie es einer sehr großen Anzahl von Datensätzen ausschaut, kann ich nicht sagen.

Ist ENGINE=MyIsam nicht veraltet? Ich meine ich habe mal was von einer Speichergrenze von 4gb gelesen, aber hinge wohl vom Dateisystem ab.

Naja meine ich zumindest.

In der Dokumentation steht nichts und im Internet konnte ich auch nichts finden. InnoDB ist lediglich die Standardengine seit MySQL 5.5. Wenn du Transaktionen allerdings nicht brauchst, kannst du auch MyIsam verwenden.

Speichergrenze von 4 GB wird wohl die von FAT32 sein, doch FAT32 wird heut zu Tage normalerweise nicht mehr als Dateisystem für 'normale' (also nicht USB-Sticks, etc.) Festplatten eingesetzt.

 

[Dimenson]

Ja guten morgen,

ich habe gestern noch mit den Daten befüllen angefangen, das Problem nun ist, das mein MySQL-Server extrem langsam ab 2 Millionen Einträge wird. Ich sehe auch, das die CPU-Last hochgeht.

Irgendwie strange, habe dann auch nach Tuning Tips gesucht, aber brachte auch nix.

Jetzt habe ich memsql installiert und das Script läuft, bin gespannt wie hier die Serverlast ausfällt. Soll ja für große Datenmengen ausgelegt sein.


Edit: 3 Millionen Marke geknackt und der Server langweilt sich ^^

 

[Dimenson]

Sodele,

mein Resumee:

Würg ^^.

Auch mit memsql kommt man an seine Grenzen. Ich schätze mal das ich bei 100Millionen Einträge gekommen bin, bis memsql abgekackt ist.

Sobald ich nun memsql starte, will der meine Datenbank recovern und putzt ordentlich Arbeitsspeicher weg, bis gar nix mehr geht. ^^

Es ist wirklich zum heulen, es nervt mich auch, dass ich nun seit 2 Tagen nicht weiter komme.
Habt ihr vielleicht noch Ideen?

Wie könnte denn eine Echtzeit Errechnung in SQL aussehen?

Gruß

 

[Yaslaw]

Speichere die Koordinaten der PLZ und berechne die Distanz auf Anfrage. Das ist einfache Geometrie

So aus dem Kopf mit Wissen das ich 15 Jahre nicht mehr gebraucht habe)

sqrt((x1-x2)^2 + (y1-y2)^2)

Oder als SQL

SELECT
    SQRT(POW(ABS(p1.x-p2.x), 2) + POW(ABS(p1.y-p2.y), 2)) AS distance
FROM
    koord AS p1,
    koord AS p2
WHERE
    p1.plz = 12345
    AND p2.plz = 67890