El recuento de Mysql Select con ST_Contains geoespacial es muy lento con varias filas
Tengo una consulta de mysql para obtener todos los lugares contados de un área. Si consulto solo una identificación, es muy rápido, si consulto dos o más, entonces es realmente lento.
Areas.geometry y Places.location son índices ESPACIALES.
Solo hay 3 filas (todas tienen geometría compleja. La fila 3 es la más compleja) en la tabla de áreas y 3000 filas en las tiendas. Construyo un archivo sql de demostración para importar si desea probar: geospatial-exemple.sql
Algún ejemplo:
Esta consulta se ejecuta en 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 se ejecuta en 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 se está ejecutando en 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)

También intenté codificar las areas.geometry en la consulta con el MULTIPOLYGON más complejo
Esta consulta se está ejecutando en 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)

Claramente, es más rápido ejecutar varias consultas que solo una y esperar unos minutos. ¿Si alguien sabe si es un error de mysql o si hay otra forma de hacerlo? Trabajar con la consulta de combinación da los mismos resultados.
Respuestas
Según la respuesta de John Powells aquí , existe una limitación indocumentada para los índices espaciales:
Para que las funciones Contiene e Intersección funcionen correctamente y para que se utilice el índice, es necesario que una de las geometrías sea una constante. Esto no parece estar documentado, aunque todos los ejemplos que verá con MySQL con Intersects / Contiene funcionan de esta manera.
Por lo tanto, ejecutar varias consultas con un área cada una sería más rápido.
Sin embargo, si tiene los permisos para crear funciones, puede utilizar una solución alternativa ejecutando su subconsulta en una función, donde areas.geometry
ahora actuará como un 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));
Ahora
SELECT a.name, fn_getplacescount(a.geometry) AS places_count
FROM areas a WHERE a.id in (1,3);
sería similar a ejecutar cada área por separado y debería tener un tiempo de ejecución similar al de usar dos consultas separadas.
Intentaría expresarlo como una combinación y ver si MySQL lo ejecuta más rápido. No estoy seguro de si MySQL ha optimizado la unión espacial, pero sería más rápido en las bases de datos con las que trabajé.
Algo como esto (no verifiqué la sintaxis):
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;