Le décompte de Mysql Select avec ST_Contains géospatial est très lent avec plusieurs lignes

Dec 17 2020

J'ai une requête mysql pour obtenir le nombre de lieux dans une zone. Si je demande un seul identifiant, c'est vraiment rapide, si je demande deux identifiants ou plus, c'est vraiment lent.

Areas.geometry et Places.location sont des index SPATIAL.

Il n'y a que 3 lignes (toutes ont une géométrie complexe. La ligne 3 est la plus complexe) dans la table des aires et 3000 lignes dans les magasins. Je crée un fichier sql de démonstration à importer si vous voulez tester: geospatial-exemple.sql

Quelques exemples:

Cette requête s'exécute dans 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) 


Cette requête s'exécute dans 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) 


Cette requête s'exécute dans les années 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) 


J'ai également essayé de coder en dur les zones.geometry dans la requête avec le MULTIPOLYGON plus complexe

Cette requête s'exécute dans 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) 


Il est donc clairement plus rapide d'exécuter plusieurs requêtes qu'une seule et d'attendre quelques minutes. Si quelqu'un sait si c'est un bogue mysql ou s'il existe un autre moyen de le faire? Travailler avec la requête Join donne les mêmes résultats.

Réponses

1 Solarflare Dec 18 2020 at 21:02

Selon la réponse de John Powells ici , il existe une limitation non documentée pour les index spatiaux:

Pour que les fonctions Contient et Intersecte fonctionnent correctement, et pour que l'index soit utilisé, vous devez avoir l'une des géométries comme constante. Cela ne semble pas être documenté, bien que tous les exemples que vous verrez avec MySQL avec Intersects / Contains fonctionnent de cette façon.

Ainsi, exécuter plusieurs requêtes avec une zone chacune serait en effet plus rapide.

Si vous avez les autorisations pour créer des fonctions, vous pouvez cependant utiliser une solution de contournement en exécutant votre sous-requête dans une fonction, où areas.geometryagira désormais comme un paramètre constant pour 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));

À présent

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

serait similaire à l'exécution de chaque zone séparément, et devrait avoir un temps d'exécution similaire à l'utilisation de deux requêtes distinctes.

MichaelEntin Dec 18 2020 at 04:14

J'essaierais de l'exprimer comme une jointure et de voir si MySQL l'exécuterait plus rapidement. Je ne sais pas si MySQL a optimisé la jointure spatiale, mais ce serait plus rapide dans les bases de données avec lesquelles j'ai travaillé.

Quelque chose comme ça (je n'ai pas vérifié la syntaxe):

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;