地理空間ST_Containsを使用したMysqlSelectカウントは、複数の行があると非常に遅くなります

Question

エリアからすべての場所をカウントするmysqlクエリがあります。1つのIDのみをクエリする場合は非常に高速で、2つ以上のIDをクエリする場合は非常に低速です。

Areas.geometryとPlaces.locationはSPATIALインデックスです。

エリアテーブルには3行（すべて複雑なジオメトリがあります。行3はより複雑です）、ストアには3000行しかありません。テストしたい場合は、インポートするデモSQLファイルを作成します：geospatial-exemple.sql

いくつかの例：

このクエリは260msで実行されています：

    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) 

---

このクエリは320msで実行されています：

    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) 

---

このクエリは50年代に実行されています：

    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) 

---

また、より複雑なMULTIPOLYGONを使用して、クエリ内のareas.geometryをハードコーディングしようとしました。

このクエリは380msで実行されています：

    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) 

---

したがって、1つだけのクエリよりも複数のクエリを実行して、数分待つ方が明らかに高速です。誰かがそれがmysqlのバグであるかどうか、またはそれを行う別の方法があるかどうかを知っている場合はどうなりますか？結合クエリを使用すると、同じ結果が得られます。

Answer 1

John Powellsの回答によると、空間インデックスには文書化されていない制限があります。

含む関数と交差する関数が正しく機能し、インデックスが使用されるためには、ジオメトリの1つが定数である必要があります。これは文書化されていないようですが、MySQL with Intersects / Containsで表示されるすべての例はこのように機能します。

したがって、それぞれ1つの領域で複数のクエリを実行すると、実際に高速になります。

ただし、関数を作成する権限がある場合は、関数でサブクエリを実行することで回避策を使用できます。これによりareas.geometry、次の定数パラメーターとして機能します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));

今

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

各領域を個別に実行するのと同様であり、2つの個別のクエリを使用する場合と同様の実行時間が必要です。

Answer 2

私はそれを結合として表現し、MySQLがそれをより速く実行するかどうかを確認しようとします。MySQLが空間結合を最適化したかどうかはわかりませんが、私が使用したデータベースではより高速になります。

このようなもの（私は構文をチェックしませんでした）：

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;