A contagem do Mysql Select com ST_Contains geoespacial é muito lenta com várias linhas

Dec 17 2020

Eu tenho uma consulta mysql para obter a contagem de todos os lugares de uma área. Se eu consultar apenas um id é muito rápido, se eu consultar dois ids ou mais, então é muito lento.

Areas.geometry e Places.location são índices ESPACIAIS.

Existem apenas 3 linhas (todas têm geometria complexa. A linha 3 é a mais complexa) na tabela de áreas e 3000 linhas nas lojas. Eu construo um arquivo de demonstração sql para importar se você quiser testar: geospatial-exemple.sql

Alguns exemplos:

Esta consulta está sendo executada em 260 ms:

    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) 


Esta consulta está sendo executada em 320 ms:

    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) 


Esta consulta está sendo executada em 50s :

    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) 


Eu também tentei codificar o areas.geometry na consulta com o MULTIPOLYGON mais complexo

Esta consulta está sendo executada em 380 ms:

    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) 


Portanto, é claro que é mais rápido executar várias consultas do que apenas uma e esperar alguns minutos. Se alguém souber se é um bug do mysql ou se existe outra maneira de fazer isso? Trabalhar com a consulta Join fornece os mesmos resultados.

Respostas

1 Solarflare Dec 18 2020 at 21:02

De acordo com a resposta de John Powells aqui , há uma limitação não documentada para índices espaciais:

Para que as funções Contains e Intersects funcionem corretamente e para que o índice seja usado, é necessário que uma das geometrias seja uma constante. Isso não parece estar documentado, embora todos os exemplos que você verá com MySQL com Intersects / Contains funcionem dessa maneira.

Portanto, executar várias consultas com uma área cada seria de fato mais rápido.

Se você tiver permissão para criar funções, poderá, no entanto, usar uma solução alternativa executando sua subconsulta em uma função, onde areas.geometryagora atuará como um parâmetro constante para 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));

Agora

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

seria semelhante a executar cada área separadamente e deveria ter um tempo de execução semelhante ao de usar duas consultas separadas.

MichaelEntin Dec 18 2020 at 04:14

Eu tentaria expressá-lo como uma junção e ver se o MySQL o executaria mais rápido. Não tenho certeza se o MySQL otimizou a junção espacial, mas seria mais rápido nos bancos de dados com os quais trabalhei.

Algo assim (não verifiquei a sintaxe):

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;