Mysql Select count с геопространственным ST_Contains очень медленно с несколькими строками

Question

У меня есть запрос mysql, чтобы получить все места из области. Если я запрашиваю только один идентификатор, это очень быстро, если я запрашиваю два идентификатора или более, это очень медленно.

Areas.geometry и Places.location - это ПРОСТРАНСТВЕННЫЕ индексы.

Всего 3 строки (все со сложной геометрией. Строка 3 более сложная) в таблице площадей и 3000 строк в магазинах. Я создаю демонстрационный файл sql для импорта, если вы хотите протестировать: geospatial-instance.sql

Некоторые примеры:

Этот запрос выполняется за 260 мс:

    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) 

---

Этот запрос выполняется за 320 мс:

    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) 

---

Я также попытался жестко закодировать area.geometry в запросе с помощью более сложного MULTIPOLYGON

Этот запрос выполняется за 380 мс:

    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) 

---

Очевидно, что быстрее выполнить несколько запросов, чем один, и подождать несколько минут. Если кто-нибудь знает, ошибка ли это mysql или есть другой способ сделать это? Работа с запросом на соединение дает те же результаты.

Answer 1

Согласно ответу Джона Пауэлса здесь , существует недокументированное ограничение для пространственных индексов:

Для правильной работы функций Contains и Intersects и использования индекса необходимо, чтобы одна из геометрий была постоянной. Это не задокументировано, хотя все примеры, которые вы увидите с MySQL с Intersects / Contains, работают именно так.

Таким образом, выполнение нескольких запросов с одной областью действительно будет быстрее.

Если у вас есть разрешения на создание функций, вы можете использовать обходной путь, запустив подзапрос в функции, где 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);

будет аналогично запуску каждой области отдельно и должно иметь такое же время выполнения, как и использование двух отдельных запросов.

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;