El recuento de Mysql Select con ST_Contains geoespacial es muy lento con varias filas

Dec 17 2020

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

1 Solarflare Dec 18 2020 at 21:02

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.geometryahora 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.

MichaelEntin Dec 18 2020 at 04:14

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;