Jumlah Mysql Select dengan geospasial ST_Contains sangat lambat dengan beberapa baris
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
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.geometry
sekarang 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.
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;