Performanceproblem Umkreissuche

[Bobby]

Moin

ich habe folgendes Problem:

  
SELECT SQL_CALC_FOUND_ROWS users.*, dest.latitude, dest.longitude, ACOS(  
             SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))  
             + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))  
             * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))  
        ) * 6380 AS distance,  FROM users LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz') LEFT JOIN postalcodes_de dest ON (dest.postalcode=users.zip AND dest.placename=users.city) WHERE CONCAT(users.zip,users.city) IN (  
                                    SELECT CONCAT(dest.postalcode,dest.placename)  FROM postalcodes_de dest  
                                    LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz')  
                                     WHERE (ACOS(  
                                     SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))  
                                     + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))  
                                     * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))  
                                     ) * 6380 ) <=20)  ORDER BY  distance  ASC LIMIT 0,4  

Dies ist der Ansatz einer Umkreissuche. Leider dauert die IN-Clause unverhältnismäßig lange.

Das Problem wird sicherlich hier liegen: DEPENDENT SUBQUERY dest index NULL country 1724 NULL 16864 Using where; Using index; Using join buffer

Dies ist die Ausgabe "EXPLAIN" für das Subquery im "IN". Die Indexe sind aber richtig gesetzt. Dies habe ich schon nachgeprüft.

Wie könnte ich das performanter gestalten?

Gruß Bobby

[Tom]

Hello,

SELECT SQL_CALC_FOUND_ROWS users.*, dest.latitude, dest.longitude, ACOS(
             SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))
             + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))
             * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))
        ) * 6380 AS distance,  FROM users LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz') LEFT JOIN postalcodes_de dest ON (dest.postalcode=users.zip AND dest.placename=users.city) WHERE CONCAT(users.zip,users.city) IN (
                                    SELECT CONCAT(dest.postalcode,dest.placename)  FROM postalcodes_de dest
                                    LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz')
                                     WHERE (ACOS(
                                     SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))
                                     + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))
                                     * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))
                                     ) * 6380 ) <=20)  ORDER BY  distance  ASC LIMIT 0,4

>   
> Dies ist der Ansatz einer Umkreissuche. Leider dauert die IN-Clause unverhältnismäßig lange.  
  
Wenn Du so frech wärst, und anstelle eines Kreises ein Quadrat absuchen würdest, könntest Du BETWEEN verwenden und es ginge bestimmt schneller.  
  
  
  
  
Liebe Grüße aus dem schönen Oberharz  
  
  
Tom vom Berg  
![](http://selfhtml.bitworks.de/Virencheck.gif)  
  

-- 
 ☻\_  
/▌  
/ \ Nur selber lernen macht schlau  
<http://bikers-lodge.com>

[dedlfix]

Tach!

Wie könnte ich das performanter gestalten?

Ich weiß es nicht, und vielleicht übersehe ich auch etwas, aber meines Erachtens kann für die Where-Klauseln kein Index verwendet werden, weil die Bedingungen für jeden Datensatz einzeln berechnet werden müssen, sowohl beim CONCAT als auch bei der Entfernungsberechnung. Das läuft immer auf einen Full-Table-Scan hinaus.

dedlfix.

[MudGuard]

Hi,

Auch wenn es gegen die üblichen Normalisierungsregeln verstößt - manchmal muß man aus Effizienzgründen dagegen verstoßen:

Du könntest

SIN(RADIANS(postalcodes_de.latitude))
COS(RADIANS(postalcodes_de.latitude))
SIN(RADIANS(postalcodes_de.longitude))
COS(RADIANS(postalcodes_de.longitude))

einmalig berechnen und in der Tabelle postalcodes als Spalten ablegen.

Das könnte dann bei jeder Abfrage einiges an Rechenzeit sparen ...

Die postalcodes_de sind ja vermutlich Daten, die sich nicht täglich ändern.

Wenn neue postalcodes kommen, muß das halt beim import der neuen Daten in die Datenbank auch gleich berechnet werden.

cu,
Andreas

[Tom]

Hello Bobby,

SELECT SQL_CALC_FOUND_ROWS users.*, dest.latitude, dest.longitude, ACOS(
             SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))
             + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))
             * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))
        ) * 6380 AS distance,  FROM users LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz') LEFT JOIN postalcodes_de dest ON (dest.postalcode=users.zip AND dest.placename=users.city) WHERE CONCAT(users.zip,users.city) IN (
                                    SELECT CONCAT(dest.postalcode,dest.placename)  FROM postalcodes_de dest
                                    LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz')
                                     WHERE (ACOS(
                                     SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))
                                     + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))
                                     * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))
                                     ) * 6380 ) <=20)  ORDER BY  distance  ASC LIMIT 0,4

  
  
Wenn Du unbedingt beim Kreis bleiben willst, musst du aber nicht zweimal dieselbe Berechnung durchführen innerhalb eines Statements. Dafür kannst Du bei  ersten Mal eine benutzerdefinierte Variable belegen und auf diese beim zweiten Mal zugreifen.  
  
  
  
  
  
Liebe Grüße aus dem schönen Oberharz  
  
  
Tom vom Berg  
![](http://selfhtml.bitworks.de/Virencheck.gif)  
  

-- 
 ☻\_  
/▌  
/ \ Nur selber lernen macht schlau  
<http://bikers-lodge.com>

[dedlfix]

Tach!

Wenn Du unbedingt beim Kreis bleiben willst, musst du aber nicht zweimal dieselbe Berechnung durchführen innerhalb eines Statements. Dafür kannst Du bei  ersten Mal eine benutzerdefinierte Variable belegen und auf diese beim zweiten Mal zugreifen.

Siehe http://dev.mysql.com/doc/refman/5.6/en/user-variables.html ab "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement." und die folgenden Ausführungen. Besonders kritisch ist dabei, der lesende und schreibende Zugriff während der Bearbeitung eines Datensatzes. Es ist nicht garantiert, dass die Variable erst für die eine Berechnung gesetzt wird und dann für die andere gelesen wird. Es kann auch gut sein, dass sie erst gelesen wird, dabei den Wert des vorhergehenden Datensatzes enthält und die Zuweisung danach erfolgt. Die Reihenfolge der Klauseln gibt nicht zwangsweise die Ausführungsreihenfolge an.

dedlfix.

[Tom]

Hello,

Wenn Du unbedingt beim Kreis bleiben willst, musst du aber nicht zweimal dieselbe Berechnung durchführen innerhalb eines Statements. Dafür kannst Du bei  ersten Mal eine benutzerdefinierte Variable belegen und auf diese beim zweiten Mal zugreifen.

Siehe http://dev.mysql.com/doc/refman/5.6/en/user-variables.html ab "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement." und die folgenden Ausführungen. Besonders kritisch ist dabei, der lesende und schreibende Zugriff während der Bearbeitung eines Datensatzes. Es ist nicht garantiert, dass die Variable erst für die eine Berechnung gesetzt wird und dann für die andere gelesen wird. Es kann auch gut sein, dass sie erst gelesen wird, dabei den Wert des vorhergehenden Datensatzes enthält und die Zuweisung danach erfolgt. Die Reihenfolge der Klauseln gibt nicht zwangsweise die Ausführungsreihenfolge an.

Tja, MySQL ist schon Mist.

Dann kann er immer noch eine Stored Routine daraus machen. Da ist das dann mMn aber erlaubt, in einem Statement erst die Berechnungen durchzuführen und im im zweiten darauf zuzugreifen.

Allerdings weiß ich jetzt nicht, wie groß die Ergebnismenge werden darf bei MySQL Stored Routines...

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Tom]

Hello,

Siehe http://dev.mysql.com/doc/refman/5.6/en/user-variables.html ab "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement." und die folgenden Ausführungen. Besonders kritisch ist dabei, der lesende und schreibende Zugriff während der Bearbeitung eines Datensatzes. Es ist nicht garantiert, dass die Variable erst für die eine Berechnung gesetzt wird und dann für die andere gelesen wird. Es kann auch gut sein, dass sie erst gelesen wird, dabei den Wert des vorhergehenden Datensatzes enthält und die Zuweisung danach erfolgt. Die Reihenfolge der Klauseln gibt nicht zwangsweise die Ausführungsreihenfolge an.

Das ist aber ein Benutzerkommentar und keine offizielle Aussage von MySQL.
Wer weiß schon, was der da wirklich getrieben hat?
Das würde ich auf jeden Fall nochmal überprüfen!

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Tach!

Siehe http://dev.mysql.com/doc/refman/5.6/en/user-variables.html ab "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement." und die folgenden Ausführungen. Besonders kritisch ist dabei, der lesende und schreibende Zugriff während der Bearbeitung eines Datensatzes. Es ist nicht garantiert, dass die Variable erst für die eine Berechnung gesetzt wird und dann für die andere gelesen wird. Es kann auch gut sein, dass sie erst gelesen wird, dabei den Wert des vorhergehenden Datensatzes enthält und die Zuweisung danach erfolgt. Die Reihenfolge der Klauseln gibt nicht zwangsweise die Ausführungsreihenfolge an.

Das ist aber ein Benutzerkommentar und keine offizielle Aussage von MySQL.

Das ist meine Aussage, die ich anhand des Handbuchtexts (dem zitierten und den nachfolgenden Sätzen), ohne die Benutzerkommentare beachtet zu haben, und meinem (Halb-)Wissen getroffen habe. Und ich denke auch nicht, dass das ein alleiniges MySQL-Problem sein soll. Die Reihenfolge der Abarbeitung der Klauseln eines SQL-Statments ist nicht definiert. Man kann es sich logisch zusammenreimen, was generell an Klauseln zuerst und was danach erfolgen muss, aber was innerhalb einer Klausel und was der Optimizer am Ende in welcher Reihenfolge ausführt, ist nicht wirklich beeinflussbar.

dedlfix.

[Tom]

Hello,

nochmal zum Thema:

  
SELECT @a, @a:=@a+1  
  

Obiges ist auch dusselig.
Man muss der Benutzervariablen schon einen Wert zuweisen bei der Deklaration.

  
SELECT @a:=1, @a:=@a+1  
  

Ich würde es an Bobbys Stelle also auf jeden Fall ausprobieren.
Ich habe dmit bisher überhaupt nie Probleme gehabt und benutze es in diversen Statemants zur Feststellung von Aufsetzpunkten in sortierten Ausgaben. (Listen von Duplicates)

Das hätte für mich verheerende Folgen, wenn es jetzt auf einmal nicht mehr funktionieren würde!

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Tach!

SELECT @a, @a:=@a+1

> Obiges ist auch dusselig.  
> Man muss der Benutzervariablen schon einen Wert zuweisen bei der Deklaration.  
  
Muss man nicht, jedenfalls gibts ohne Erstzuweisung keinen Fehler. @a ist dann nur NULL. Außerdem ist das nur eine Zuweisung. Es ist nicht gesagt, dass nicht noch anderswo eine Erst-Zuweisung erfolgt. Eine Deklaration ist nicht erforderlich.  
  

> ~~~sql
  

> SELECT @a:=1, @a:=@a+1  
> 

Das ist nicht dasselbe wie obiges Statement. Ziel ist anscheinend, dass im ersten Ergebnisgfeld der bisherige Wert ausgegeben wird und nicht irgendwas jetzt erst zugewiesenes. Im zweiten soll anschließend die Inkrementierung stattfinden.

Ich würde es an Bobbys Stelle also auf jeden Fall ausprobieren.

Ich würde mich an Bobbys Stelle auch nicht nach Ausprobieren darauf verlassen, dass es so bleibt.

Anderes Beispiel: Nichtmal auf die EXPLAIN-Ausführungsplan-Informationen kann man sich verlassen, wenn sich der Datenbestand ändert. Bei drei Datensätzen ist ein Fulltable-Scan kein Problem, bei sehr vielen lohnt sich die Verwendung eines Index eher. Somit kann es beim selben Statement zu unterschiedliche Ausführungen kommen.

Ich habe dmit bisher überhaupt nie Probleme gehabt und benutze es in diversen Statemants zur Feststellung von Aufsetzpunkten in sortierten Ausgaben. (Listen von Duplicates)

Und du bist dir auch sicher, dass in deinen Fällen ebenfalls die Zuweisung und die Verwendung auf SELECT und WHERE verteilt ist? In dem Fall müsste noch dazu irgendwer garantieren, dass die Select-Ausdrücke gleichzeitig zum Filter-Vorgang des WHERE berechnet werden, und nicht zuerst die Datenmenge in einem Rutsch eingeschränkt wird und anschließend erst die SELECT-Ausdrücke berechnet werden.

dedlfix.

[Tom]

Hello,

SELECT @a:=1, @a:=@a+1

>   
> Das ist nicht dasselbe wie obiges Statement.  
  
Stimmt. Hier wird @a definiert.  
  
Anderenfalls würde ein altes @a gesucht, das üblicherweise aus der vorherigen Treffer-Row stammt oder einem vorhergegangenen Select derselben Connection.  
  
  
  
  
  
Liebe Grüße aus dem schönen Oberharz  
  
  
Tom vom Berg  
![](http://selfhtml.bitworks.de/Virencheck.gif)  
  

-- 
 ☻\_  
/▌  
/ \ Nur selber lernen macht schlau  
<http://bikers-lodge.com>

[dedlfix]

Tach!

SELECT @a:=1, @a:=@a+1

> > Das ist nicht dasselbe wie obiges Statement.  
> Stimmt. Hier wird @a definiert.  
> Anderenfalls würde ein altes @a gesucht, das üblicherweise aus der vorherigen Treffer-Row stammt oder einem vorhergegangenen Select derselben Connection.  
  
Oder eher wahrscheinlich: aus einer vorhergehenden Zuweisung mit SET.  
  
  
dedlfix.

[JürgenB]

Hallo Bobby,

nur eine Idee (so mache ich es, aber nicht mit Daten aus einer Datenbank):

lege einmal, z.B. beim Anlegen der Daten, für jeden Ort eine Liste mit den Entfernungen zu allen anderen Orten an. Wenn du diese noch sortierst, ist die Umkreissuche nur noch ein einmaliger Durchlauf durch diese Liste bis zur gewünschten Entfernung.

Gruß, Jürgen

[Jörg Reinholz]

Wie könnte ich das performanter gestalten?

Wie ich schon ein paar mal ausführte: Erst das Viereck mit dem billigen between abfragen und dann aus dem Resultat die Orte mit der teuren Berechnung des Kreises raussuchen.

Jörg Reinholz

[Bobby]

Moin

SELECT SQL_CALC_FOUND_ROWS users.*, dest.latitude, dest.longitude, ACOS(
             SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))
             + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))
             * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))
        ) * 6380 AS distance,  FROM users LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz') LEFT JOIN postalcodes_de dest ON (dest.postalcode=users.zip AND dest.placename=users.city) WHERE CONCAT(users.zip,users.city) IN (
                                    SELECT CONCAT(dest.postalcode,dest.placename)  FROM postalcodes_de dest
                                    LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz')
                                     WHERE (ACOS(
                                     SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))
                                     + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))
                                     * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))
                                     ) * 6380 ) <=20)  ORDER BY  distance  ASC LIMIT 0,4

>   
  
Manchmal sieht man den Walt vor lauter Bäumen nicht. Die Lösung brauch sich ja nur auf die Distance zu beziehen. Oder sehe ich das falsch?  
  
~~~sql
  
 SELECT SQL_CALC_FOUND_ROWS users.*, ACOS(  
              SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))  
              + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))  
              * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))  
         ) * 6380 AS distance  FROM users LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz') LEFT JOIN postalcodes_de dest ON (dest.postalcode=users.zip AND dest.placename=users.city) WHERE  
(ACOS(  
 SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))  
 + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))  
 * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))  
 ) * 6380 ) <=20  
 ORDER BY  distance  ASC LIMIT 0,4  
 

Habe ich etwas übersehen oder ist es wirklich so einfach? :o ^^

Gruß Bobby

[Bobby]

Moin

Hallo? Keiner mehr Zeit mal kurz drauf zu schauen?

SELECT SQL_CALC_FOUND_ROWS users.*, ACOS(
              SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))
              + COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))
              * COS(RADIANS(src.longitude) - RADIANS(dest.longitude))
         ) * 6380 AS distance  FROM users LEFT JOIN postalcodes_de src ON (src.postalcode='01855' AND src.placename='Sebnitz') LEFT JOIN postalcodes_de dest ON (dest.postalcode=users.zip AND dest.placename=users.city) WHERE
(ACOS(
SIN(RADIANS(src.latitude)) * SIN(RADIANS(dest.latitude))

• COS(RADIANS(src.latitude)) * COS(RADIANS(dest.latitude))

• COS(RADIANS(src.longitude) - RADIANS(dest.longitude))
  ) * 6380 ) <=20
  ORDER BY  distance  ASC LIMIT 0,4

>   

Habe ich etwas übersehen oder ist es wirklich so einfach? :o ^^  
  
Gruß Bobby  

-- 
-> Für jedes Problem gibt es eine Lösung, die einfach, sauber und falsch ist! <-  
### Henry L. Mencken ###  
-> Nicht das Problem macht die Schwierigkeiten, sondern unsere Sichtweise! <-  
### Viktor Frankl ###  
  
ie:{ br:> fl:{ va:} ls:< fo:) rl:( n4:( de:> ss:) ch:? js:( mo:} sh:) zu:)