Unterabfrage Zeitintesiv ? Alternativen ?

[Master0Blicker]

Hallo Forum, ich hab da wieder ein Problem!

Ich habe eine riesiges SQL-Statement (wird generiert per PHP). Das nur mal so nebenbei....

Ich habe eine Tabelle mit geodaten (Längengrade, Breitengrade und PLZ) und nun möchte ich dem SQL-Statment bei bedarf eine weitere Where-Klausel anhängen die mir nur Resultate liefert wenn die sich in einem bestimmten Gebiet aufhalten.

Per PLZ schaue ich in die DB um die Koordinaten zu bekommen.
Nun wende ich eine komplizierte Formel an die mir alle Postleitzahlen im angegebenen Radius berechnet und zurückliefert.
So weit so gut.

Nun habe ich dieses in eine Unterabfrage verpackt:

..where plz in(Select plz from geodat where ...Formel)..

So, nun meine Frage:

Ist dies nicht ziemlich Zeitaufwendig wenn ich das per Subselect löse ?
Gibt es eine weitere Möglichkeit dies zu realisieren die eventuell schneller funzt ?
Per inner Join die Tabelle zu verknüpfen geht nicht da ich nicht vom jeweiligen Datensatz die Breiten und Längengrade benutzen will sondern eine feste allgemeingültige (vorher in erfahrung gebracht) benutze.

Aber die Tabelle per inner join mit einzubeziehen (also ohne Verknüpfung mit ON-Bedingung) funktioniert nicht weil a) alles sehr lange dauert (ca. 19 Sekunden) und b) ich doppelt gemoppelte Resultate widerbekomme und zwar so viele wie die Geodaten-Tabelle Einträge hat.....ist ja klar....

Hat einer von euch eine Idee oder soll ich das mit dem Subselekt so lassen ? Mich würde es wurmen wenn ich das so lasse aber weis das das noch eleganter bzw. besser und schneller funktionieren könnte (auch wenn es jetzt nichts zu bemängeln gibt bei der Abfrage, aber wie sieht es aus wenn viele darauf abfragen ?)


Hoffe ihr habt mein Problem sowiet verstanden......

[Titus]

Re: Unterabfrage Zeitintesiv ? Alternativen ?

OffTopic:

---

Original geschrieben von Master0Blicker
Hoffe ihr habt mein Problem sowiet verstanden......

hoff ich auch!

---

Ohne jetzt zu wissen, wie deine Formel aussieht: Aber eine Umkreissuche bei vorhandenen Koordinaten in einer Gaußschen Ebene ist doch ziemlich simpel ... und den größten Geschwindigkeitsgewinn erzielt man meist durch optimale Algorithmen.

Erinnern wir uns an den Mathe-Unterricht der 10. Klasse (opder war´s die 9.?) zum Thema rechtwinklige Dreiecke.

Satz des Pythagoras: Das Quadrat der Hypothenuse ist gleich der Summe der Kathetenquadrate.
In deinem Fall sind die Katheten die Differenz der Koordinaten deiner beiden Punkte (x0,y0) und (x1,y1); die Länge der Hypothenuse ist der Abstand d der beiden Punkte voneinander.

d^2 = (x1-x0)^2 + (y1-y0)^2 () <==> d = sqrt(pow(x1-x0, 2) + pow(y1-y0, 2))
Bei Integern ist vermutlich d = sqrt((x1-x0)*(x1-x0) + (y1-y0)*(y1-y0)) schneller.
Wenn man vorher die Entfernung quadriert, kann sogar das Wurzelziehen entfallen.

Auf jeden Fall sollte der Ausdruck plz like '$plzgebiet%' vor der Entfernungsformel stehen.

Und möglicherweise hilft es bezüglich der Geschwindigkeit auch, den Entfernungsvergleich als having-Klausel einzusetzen; die Kommentare zur Query-Optimierung sind da nicht ganz klar:

Quelle: http://dev.mysql.com/doc/mysql/en/wh...mizations.html
- HAVING is merged with WHERE if you don't use GROUP BY or group functions (COUNT(), MIN(), and so on).
- Before each record is output, those that do not match the HAVING clause are skipped.

Was das bezüglich Zeit- und Speicherbedarf heißt, kann ich Dir leider nicht sagen; aber im Zweifelsfall geht Probieren über Studieren.


Was die Subquery angeht: Entweder du machst zwei davon (eine für x, eine für y des Mittelpunktes) oder du setzt eine separate Query ab, in der du beide Werte holst (mein Favorit).


Mein Ansatz sähe so aus:

list($x0,$y0) = mysql_fetch_row(mysql_query(
  "select x,y from geodb where ... "
));
$result = mysql_query("select * from geodb
  where plz like '$plzgebiet%'
  having (x-$x0)*(x-$x0) + (y-$y0)*(y-$y0)<". ($entfernung*$entfernung)
); 

[Master0Blicker]

Also das mit der Umkreissuche habe ich ja bereits und das ist kein Problem...(übrigens, deine Formel wird nicht klappen, nur so mal nebenbei....benutze diese:

(ACOS((SIN(RADIANS($breite))*SIN(RADIANS(breite))) + (COS(RADIANS($breite))*COS(RADIANS(breite))*COS(RADIANS(laenge)-RADIANS($laenge)))) * 6378.388) <= $radius

)

Dein Favorit war auch meiner, habe eine SQL-Query davor gesetzt die mit die Koordinaten des Ortes mit angegebener PLZ holt. Diese setute ich in die Variable $breite und $laenge ein. Jetzt nur noch vom Benutzer auswählen lassen welchen Radius er möchte und diesen in $radius reinsetzten. Und das wars -> funktioniert wunderbar!

Nur war mein Problem was anderes!
Ich habe Sorge wegen der Geschwindigkeit des ganzen! Vor allem weil ich auch ein Subselect benutze (in dem sich diese Formel auch befindet). Wenn ich das so teste geht das perfekt schnell. Ich denke aber daran was passieren wird wenn plötzlich viele auf einmal diese Abfrage auf der Webseite benutzen ! Daher möchte ich mein Code natürlich optimal halten das ich weis das es eben schneller nicht geht als das was ich da gemacht habe.......

Also nochmal meine Frage:
Ist Subselect zu empfehlen (vor allem ist es ein SQL-Statement der über 5 Tabellen verknüpft wird) und wenn nicht was wäre die Alternative ?

[thommyk]

guck mal in diesen thread
http://www.php-resource.de/forum/sho...threadid=55439

Ist ungefähr das gleiche Problem. Da ist alles mit einer Query gelöst.

Alternative wäre bei Dir sonst, die Subquery auszulagern, also vorher zu machen, die Plz in ein array zu packen und dann

..where plz in ('".implode("','", $plzarray)."') 

Ansonsten gilt

Was das bezüglich Zeit- und Speicherbedarf heißt, kann ich Dir leider nicht sagen; aber im Zweifelsfall geht Probieren über Studieren

wie Titus schon sagt