[mysql] optymalizacja kodu Opcje

[crocodillo]

Witam.

Mam taką funkcję:

[PHP] pobierz, plaintext 
function n() {
        $n=sql::go(sprintf('SELECT * FROM 
                            (SELECT id as i1, latitude as x1, longitude as y1 FROM %1$s) as t1,
                            (SELECT id as i2, latitude as x2, longitude as y2 FROM %1$s) as t2
                            WHERE i1<i2 AND
                            i1 NOT IN (SELECT id1 from %2$s WHERE id1 = i1 AND id2 = i2)
                            LIMIT 3000',self::$tab_places,self::$tab_places_distance));
        echo '<pre>';
        if ($n) {
            while ($a=mysql_fetch_assoc($n)) {
                $ar=array(
                    'id1'   => $a['i1'],
                    'id2'   => $a['i2'],
                    'distance'  => round(self::distanceFromCoords($a['x1'],$a['y1'],$a['x2'],$a['y2'])*1000)
                );
                print "$a[i1] - $a[i2] : $ar[distance]".PHP_EOL;
                sql::insertArr(self::$tab_places_distance,$ar);
            }
        }
}
[PHP] pobierz, plaintext 

Nie będę tłumaczył wszystkiego, osoby znające się zrozumieją na pewno (po nazwach funkcji wiadomo o co chodzi).

Chodzi o to, że w jednej tabeli mam miejscowości wraz ze współrzędnymi geograficznymi, a w drugiej odległości między miejscowościami. W ten sposób jak ja to rozwiązałem to strasznie wolno chodzi (obecnie w bazie jest ok 1000 miejscowości). Co prawda obliczam tylko raz, ew. aktualizacja w przyszłości po dodaniu nowych miejscowości, ale tak z ciekawości chciałbym was zapytać, czy widzicie jakieś lepsze rozwiązanie?

Tak jak mam w przykładzie dla LIMIT 3000 u mnie na laptopie funkcja wykonuje się ponad 15 sekund. Kiedyś to zrobiłem bardziej w php, przeleciałem całą tabelę w pętlach, ale dzięki temu sposobowi łatwiej mi jest dodawać nowe miejscowości.

[ActivePlayer]

jak wyglada fu.kcja distance from coords. Nie moglbys jej na mysql przerzucic?

[crocodillo]

Właśnie jest tam trochę obliczeń:

[PHP] pobierz, plaintext 
public function distanceFromCoords($lat1, $lan1, $lat2, $lan2) {
 
        $lat1=deg2rad($lat1);
        $lat2=deg2rad($lat2);
        $lan1=deg2rad($lan1);
        $lan2=deg2rad($lan2);
 
        return rad2deg(acos(sin($lat1) * sin($lat2) +  cos($lat1) * cos($lat2) * cos($lan1-$lan2)))*111.18957696;
    }
[PHP] pobierz, plaintext 

Bałem się nawet myśleć o tym, żeby obliczenia robić w mysql (IMG:style_emoticons/default/smile.gif)

Nawet nie chciałem próbować zrobić tego tylko w mysql, a tu proszę jak szybko poszło: całą bazę (niecałe 500tys) wygenerowane w niecałe 20 sekund:

[PHP] pobierz, plaintext 
sql::go(sprintf(
            'INSERT INTO %2$s (id1,id2,distance) 
            (SELECT i1, i2, DEGREES(acos(sin(x1) * sin(x2) +  cos(x1) * cos(x2) * cos(y1-y2)))*111189.57696
                FROM
                    (SELECT id as i1, RADIANS(latitude) as x1, RADIANS(longitude) as y1 FROM %1$s) as t1,
                    (SELECT id as i2, RADIANS(latitude) as x2, RADIANS(longitude) as y2 FROM %1$s) as t2
                    WHERE i1<i2 AND
                    i1 NOT IN (SELECT id1 from %2$s WHERE id1 = i1 AND id2 = i2)
            )',self::$tab_places,self::$tab_places_distance));
[PHP] pobierz, plaintext 

Ma ktoś może teraz sugestie, jak zoptymalizować to zapytanie?

[ActivePlayer]

Kod

explain SELECT i1, i2, DEGREES(acos(sin(x1) * sin(x2) +  cos(x1) * cos(x2) * cos(y1-y2)))*111189.57696
                FROM
                    (SELECT id as i1, RADIANS(latitude) as x1, RADIANS(longitude) as y1 FROM %1$s) as t1,
                    (SELECT id as i2, RADIANS(latitude) as x2, RADIANS(longitude) as y2 FROM %1$s) as t2
                    WHERE i1<i2 AND
                    i1 NOT IN (SELECT id1 from %2$s WHERE id1 = i1 AND id2 = i2

wykonaj i wklej wynik

[crocodillo]

Dzięki, już daję wynik:

[PHP] pobierz, plaintext 
Array
(
    [id] => 1
    [select_type] => PRIMARY
    [table] => 
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 904
    [Extra] => 
)
Array
(
    [id] => 1
    [select_type] => PRIMARY
    [table] => 
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 904
    [Extra] => Using where; Using join buffer
)
Array
(
    [id] => 4
    [select_type] => DEPENDENT SUBQUERY
    [table] => common_places_pl_dist
    [type] => ref
    [possible_keys] => id1
    [key] => id1
    [key_len] => 8
    [ref] => t1.i1,t2.i2
    [rows] => 1
    [Extra] => Using where; Using index
)
Array
(
    [id] => 3
    [select_type] => DERIVED
    [table] => common_places_pl
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 904
    [Extra] => 
)
Array
(
    [id] => 2
    [select_type] => DERIVED
    [table] => common_places_pl
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 904
    [Extra] => 
)
[PHP] pobierz, plaintext 

[ActivePlayer]

bez danych nie potrafię Ci pomóc. nie wiem czy nie jesteś w stanie zamienić tych podzapytań na zwykłego joina, i druga sprawa jak robisz RADIANS(latitude) as x1 to za każdym razem mysql musi to policzyć, być może wyda się lepsze żeby te dane trzymać w dodatkowych kolumnach żeby nie trzeba było ich liczyć za każdym razem.

[crocodillo]

Dzięki. Odnośnie radianów, to wolę je liczyć, bo wykorzystuję je tylko i wyłącznie przy obliczaniu właśnie tych odległości, a że nieczęsto będzie to liczone, to wolę stracić na czasie. W sumie to już zostawiam tak jak mam, teraz nowy problem mi wyskoczył, może rano po przebudzeniu mnie olśni.

[Bags_Bunny]

Kod

WHERE i1<i2 AND i1 NOT IN (SELECT id1 from %2$s WHERE id1 = i1 AND id2 = i2)

To nie wygląda najlepiej, a przy Twojej strukturze zapytania z indeksami cięzko. Polecam pozbyć się NOT IN albo zmienić zapytanie tak, żeby nie generowało tymczasowych danych, tylko operowało na istniejących tabelach. i1<i2 też lekkie nie jest.