Mysql Select count z geospatial ST_Contains jest bardzo powolna z wieloma wierszami
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
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.geometry
bę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ń.
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;