A contagem do Mysql Select com ST_Contains geoespacial é muito lenta com várias linhas
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
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.geometry
agora 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.
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;