Mysql Select count z geospatial ST_Contains jest bardzo powolna z wieloma wierszami

Dec 17 2020

Mam zapytanie mysql, aby uzyskać liczbę wszystkich miejsc z obszaru. Jeśli szukam tylko jednego identyfikatora, jest to naprawdę szybkie, jeśli pytam o dwa lub więcej identyfikatorów, to jest bardzo wolne.

Areas.geometry i Places.location to indeksy PRZESTRZENNE.

Istnieją tylko 3 rzędy (wszystkie mają złożoną geometrię. Wiersz 3 jest bardziej złożony) w tabeli obszarów i 3000 wierszy w sklepach. Tworzę demo plik sql do zaimportowania, jeśli chcesz przetestować: geospatial-exemple.sql

Oto kilka przykładów:

To zapytanie działa w 260 ms:

    select  a.name, 
            (
            SELECT  count(*)
                FROM  places p
                WHERE  ST_Contains(a.geometry,p.location)
            ) as places_count
        FROM  areas a
        WHERE  a.id in (1) 


To zapytanie działa w 320 ms:

    select  a.name, 
            (
            SELECT  count(*)
                FROM  places p
                WHERE  ST_Contains(a.geometry,p.location)
            ) as places_count
        FROM  areas a
        WHERE  a.id in (3) 


To zapytanie działa za 50s :

    select  a.name, 
            (
            SELECT  count(*)
                FROM  places p
                WHERE  ST_Contains(a.geometry,p.location)
            ) as places_count
        FROM  areas a
        WHERE  a.id in (1,3) 


Próbowałem również na stałe zakodować area.geometry w zapytaniu za pomocą bardziej złożonego MULTIPOLYGON

To zapytanie działa w 380 ms:

    select  a.name, 
            (
            SELECT  count(*)
                FROM  places p
                WHERE  ST_Contains(ST_GeomFromText("MULTIPOLYGON((...))",
                                    4326,
                                    'axis-order=long-lat'),p.location)
            ) as places_count
        FROM  areas a
        WHERE  a.id in (1,3) 


Więc oczywiście szybsze jest uruchamianie wielu zapytań niż tylko jedno i czekanie przez kilka minut. Jeśli ktoś wie, czy to błąd mysql, czy jest na to inny sposób? Praca z zapytaniem Join daje te same wyniki.

Odpowiedzi

1 Solarflare Dec 18 2020 at 21:02

Zgodnie z odpowiedzią Johna Powellsa , istnieje nieudokumentowane ograniczenie dotyczące indeksów przestrzennych:

Aby funkcje Contains i Intersects działały poprawnie, a aby indeks był używany, musisz mieć stałą jedną z geometrii. Wygląda na to, że nie jest to udokumentowane, chociaż wszystkie przykłady, które zobaczysz w MySQL z przecięciami / zawartymi, działają w ten sposób.

Zatem uruchomienie wielu zapytań z jednym obszarem byłoby rzeczywiście szybsze.

Jeśli masz uprawnienia do tworzenia funkcji, możesz jednak zastosować obejście, uruchamiając podzapytanie w funkcji, która areas.geometrybędzie teraz działać jako stały parametr dla ST_Contains():

CREATE FUNCTION fn_getplacescount(_targetarea GEOMETRY) 
RETURNS INT READS SQL DATA
RETURN (SELECT COUNT(*) FROM places p WHERE ST_Contains(_targetarea, p.location));

Teraz

SELECT a.name, fn_getplacescount(a.geometry) AS places_count 
FROM areas a WHERE a.id in (1,3);

byłoby podobne do uruchamiania każdego obszaru osobno i powinno mieć podobny czas wykonania, jak przy użyciu dwóch oddzielnych zapytań.

MichaelEntin Dec 18 2020 at 04:14

Spróbowałbym wyrazić to jako złączenie i sprawdzić, czy MySQL uruchomi go szybciej. Nie jestem pewien, czy MySQL ma zoptymalizowane łączenie przestrzenne, ale byłoby to szybsze w bazach danych, z którymi pracowałem.

Coś takiego (nie sprawdzałem składni):

SELECT areas.name, count(*) as places_count
FROM places p JOIN areas a
ON ST_Contains(a.geometry, p.location)
WHERE a.type = "city"
GROUP BY 1;