Jumlah Mysql Select dengan geospasial ST_Contains sangat lambat dengan beberapa baris

Dec 17 2020

Saya memiliki kueri mysql untuk menghitung semua tempat dari suatu area. Jika saya meminta hanya satu id, itu sangat cepat, jika saya meminta dua id atau lebih maka itu sangat lambat.

Area.geometry dan Places.location adalah indeks SPASIAL.

Hanya ada 3 baris (semua memiliki geometri kompleks. Baris 3 lebih kompleks) di tabel area dan 3000 baris di toko. Saya membuat file demo sql untuk diimpor jika Anda ingin menguji: geospatial-exemple.sql

Beberapa contoh:

Kueri ini berjalan dalam 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) 


Kueri ini berjalan dalam 320 md:

    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) 


Kueri ini berjalan dalam 50-an :

    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) 


Saya juga mencoba melakukan hardcode area.geometry dalam kueri dengan MULTIPOLYGON yang lebih kompleks

Kueri ini berjalan dalam 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) 


Jadi jelas lebih cepat menjalankan beberapa kueri daripada hanya satu dan menunggu beberapa menit. Jika ada yang tahu apakah itu bug mysql atau jika ada cara lain untuk melakukannya? Bekerja dengan kueri Gabung memberikan hasil yang sama.

Jawaban

1 Solarflare Dec 18 2020 at 21:02

Menurut jawaban John Powells di sini , ada batasan tidak terdokumentasi untuk indeks spasial:

Agar fungsi Contains dan Intersects berfungsi dengan baik, dan agar indeks dapat digunakan, Anda harus membuat salah satu geometri menjadi konstanta. Ini tampaknya tidak didokumentasikan, meskipun semua contoh yang akan Anda lihat dengan MySQL dengan Intersects / Contains berfungsi dengan cara ini.

Jadi menjalankan beberapa kueri dengan masing-masing satu area memang akan lebih cepat.

Jika Anda memiliki izin untuk membuat fungsi, Anda dapat menggunakan solusi dengan menjalankan subkueri Anda dalam sebuah fungsi, di mana areas.geometrysekarang akan bertindak sebagai parameter konstan untuk 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));

Sekarang

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

akan mirip dengan menjalankan setiap area secara terpisah, dan harus memiliki waktu eksekusi yang sama seperti menggunakan dua kueri terpisah.

MichaelEntin Dec 18 2020 at 04:14

Saya akan mencoba mengungkapkannya sebagai gabungan dan melihat apakah MySQL menjalankannya lebih cepat. Tidak yakin apakah MySQL telah mengoptimalkan gabungan spasial, tetapi akan lebih cepat dalam database yang saya gunakan.

Sesuatu seperti ini (saya tidak memeriksa sintaksnya):

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;