Jeo uzamsal ST_Contains ile Mysql Select sayımı birden çok satırda çok yavaş
Bir alandaki tüm yerlerin sayımını almak için bir mysql sorgum var. Yalnızca bir kimlik için sorgu yaparsam gerçekten hızlıdır, iki veya daha fazla kimlik için sorgu yaparsam gerçekten yavaş olur.
Fields.geometry ve Places.location SPATIAL dizinleridir.
Alanlar tablosunda yalnızca 3 satır (tümü karmaşık geometriye sahiptir. Satır 3 daha karmaşıktır) ve mağazalarda 3000 satır vardır. Test etmek istiyorsanız içe aktarmak için bir demo sql dosyası oluşturuyorum: geospatial-exemple.sql
Bazı örnekler:
Bu sorgu 260 ms'de çalışıyor:
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)

Bu sorgu 320 ms'de çalışıyor:
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)

Bu sorgu 50'li yıllarda çalışıyor :
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)

Ayrıca, daha karmaşık MULTIPOLYGON ile sorguda alanlar.geometry'yi de kodlamayı denedim.
Bu sorgu 380 ms'de çalışıyor:
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)

Dolayısıyla, birden çok sorgu çalıştırıp bir dakika beklemekten daha hızlıdır. Birisi bunun bir mysql hatası olup olmadığını biliyorsa veya bunu yapmanın başka bir yolu varsa? Join sorgusuyla çalışmak aynı sonuçları verir.
Yanıtlar
John Powells'in buradaki cevabına göre , uzamsal indeksler için belgelenmemiş bir sınırlama vardır:
İçerir ve Kesişim işlevlerinin düzgün çalışması için ve dizinin kullanılması için geometrilerden birinin sabit olması gerekir. Intersects / Contains ile MySQL ile göreceğiniz tüm örnekler bu şekilde çalışsa da, bu belgelenmiş görünmüyor.
Bu nedenle, her biri bir alan içeren birden çok sorgu çalıştırmak gerçekten daha hızlı olacaktır.
Eğer işlevler oluşturmak için izinleri varsa, bununla birlikte bir fonksiyonu, içinde alt sorgu çalıştırarak bir çözümü kullanabilirsiniz areas.geometry
şimdilik sabit parametre olarak hareket edecektir 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));
Şimdi
SELECT a.name, fn_getplacescount(a.geometry) AS places_count
FROM areas a WHERE a.id in (1,3);
her alanı ayrı ayrı çalıştırmaya benzer ve iki ayrı sorgu kullanan benzer bir yürütme süresine sahip olmalıdır.
Bunu bir birleştirme olarak ifade etmeye ve MySQL'in daha hızlı çalışıp çalışmadığını görmeye çalışırdım. MySQL'in uzamsal birleştirmeyi optimize edip etmediğinden emin değilim, ancak çalıştığım veritabanlarında daha hızlı olurdu.
Bunun gibi bir şey (sözdizimini kontrol etmedim):
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;