Mysql Select count mit geospatial ST_Contains ist mit mehreren Zeilen sehr langsam

Question

Ich habe eine MySQL-Abfrage, um alle Orte aus einem Bereich zu zählen. Wenn ich nur nach einer ID frage, ist es sehr schnell. Wenn ich nach zwei oder mehr IDs frage, ist es sehr langsam.

Areas.geometry und Places.location sind SPATIAL-Indizes.

Es gibt nur 3 Zeilen (alle haben eine komplexe Geometrie. Die Zeile 3 ist die komplexere) in der Bereichstabelle und 3000 Zeilen in den Geschäften. Ich erstelle eine Demo-SQL-Datei zum Importieren, wenn Sie testen möchten: geospatial-exemple.sql

Einige Beispiele:

Diese Abfrage wird in 260 ms ausgeführt:

    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) 

---

Diese Abfrage wird in 320 ms ausgeführt:

    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) 

---

Diese Abfrage wird in den 50er Jahren ausgeführt :

    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) 

---

Ich habe auch versucht, die area.geometry in der Abfrage mit dem komplexeren MULTIPOLYGON fest zu codieren

Diese Abfrage wird in 380 ms ausgeführt:

    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) 

---

Es ist also eindeutig schneller, mehrere Abfragen als nur eine auszuführen und eine Minute zu warten. Wenn jemand weiß, ob es sich um einen MySQL-Fehler handelt oder ob es einen anderen Weg gibt, dies zu tun? Wenn Sie mit der Join-Abfrage arbeiten, erhalten Sie dieselben Ergebnisse.

Answer 1

Laut der Antwort von John Powells gibt es hier eine undokumentierte Einschränkung für räumliche Indizes:

Damit die Funktionen "Enthält" und "Schnittpunkte" ordnungsgemäß funktionieren und der Index verwendet werden kann, muss eine der Geometrien eine Konstante sein. Dies scheint nicht dokumentiert zu sein, obwohl alle Beispiele, die Sie mit MySQL mit Intersects / Contains sehen, auf diese Weise funktionieren.

Das Ausführen mehrerer Abfragen mit jeweils einem Bereich wäre also in der Tat schneller.

Wenn Sie über die Berechtigungen zum Erstellen von Funktionen verfügen, können Sie jedoch eine Problemumgehung verwenden, indem Sie Ihre Unterabfrage in einer Funktion ausführen, areas.geometrydie nun als konstanter Parameter für Folgendes fungiert 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));

Jetzt

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

Dies ähnelt der separaten Ausführung jedes Bereichs und sollte eine ähnliche Ausführungszeit haben wie die Verwendung von zwei separaten Abfragen.

Answer 2

Ich würde versuchen, es als Join auszudrücken und zu sehen, ob MySQL es schneller ausführt. Ich bin mir nicht sicher, ob MySQL die räumliche Verknüpfung optimiert hat, aber in den Datenbanken, mit denen ich gearbeitet habe, wäre dies schneller.

So etwas (ich habe die Syntax nicht überprüft):

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;