Discussion :

[Couscouss sensei]

Bonjour,

Pour un de mes projets je rencontre régulièrement un problème pour établir des requêtes.
J'ai plusieurs tables :
event : (id, date, ..., id_lieu)
lieu : (id, nom, ..., id_ville)
villes : (id, ville, cp, slug, latitude, longitude, ..., id_dept)
departement : (id, nom, code, slug, ..., id_region)
region : (id, nom, slug, ...)

Et une procédure 'get_distance_metres' qui retourne la distance entre deux coordonnées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
BEGIN
    DECLARE rlo1 DOUBLE;
    DECLARE rla1 DOUBLE;
    DECLARE rlo2 DOUBLE;
    DECLARE rla2 DOUBLE;
    DECLARE dlo DOUBLE;
    DECLARE dla DOUBLE;
    DECLARE a DOUBLE;
 
    SET rlo1 = RADIANS(lng1);
    SET rla1 = RADIANS(lat1);
    SET rlo2 = RADIANS(lng2);
    SET rla2 = RADIANS(lat2);
    SET dlo = (rlo2 - rlo1) / 2;
    SET dla = (rla2 - rla1) / 2;
    SET a = SIN(dla) * SIN(dla) + COS(rla1) * COS(rla2) * SIN(dlo) * SIN(dlo);
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
END

Voila j'aimerais via une requête retourner toutes les villes ayant dans un rayon de 30km des events

J'ai essayé avec les events puis joindre les villes via LEFT JOIN mais la requête avec les ~33k de villes n'en finit pas ...
ex :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT E.id as event_id, D.slug AS slug_dept, R.slug AS slug_region, V.slug_ville, V.CP
FROM event E
INNER JOIN lieu L ON L.id = E.id_lieu
INNER JOIN villes V ON V.id = L.id_ville
INNER JOIN departement D ON D.id = V.id_dept
INNER JOIN region R on R.id = D.id_region
INNER JOIN villes V2 ON get_distance_metres(V2.Latitude, V2.Longitude, V.Latitude, V.Longitude) < 30000
WHERE E.date > CURDATE()
GROUP BY V.slug

[aieeeuuuuu]

Bonjour,

C'est normal que ce soit long ! Si vous avez ne serait-ce que 100 événements, votre procédure sera appelée plus de 3 millions de fois !

Vous pouvez peut-être limiter la recherche des villes dans un premier temps uniquement avec leur longitutde/latitude, qui doivent être proches. il sera alors possible d'utiliser un index pour restreindre les résultats avant de calculer plus finement la distance avec votre procédure :

Créez un index

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX IX_LAT_LONG ON Ville(latitude, longitude)

Puis modifiez votre requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT E.id AS event_id, D.slug AS slug_dept, R.slug AS slug_region, V.slug_ville, V.CP
FROM event E
INNER JOIN lieu L ON L.id = E.id_lieu
INNER JOIN villes V ON V.id = L.id_ville
INNER JOIN departement D ON D.id = V.id_dept
INNER JOIN region R ON R.id = D.id_region
INNER JOIN villes V2 
    ON get_distance_metres(V2.Latitude, V2.Longitude, V.Latitude, V.Longitude) < 30000
    AND V2.latitude BETWEEN V1.latitude - 0.3 AND V1.latitude + 0.3
    AND V2.longitude BETWEEN V1.longitude - 0.3 AND V1.longitude + 0.3
WHERE E.date > CURDATE()
GROUP BY V.slug

en espérant que MySQL sache en tirer partie

[aieeeuuuuu]

Autre chose.

Ce sera surement peanuts, mais vous pourriez stocker directement les radians de vos latitudes et longitudes dans votre table (si vous n'utilisez pas ces colonnes pour autre chose que ce type de recherche). ça éviterait de refaire les 4 calculs à chaque fois dans votre procédure. Toujours avec 100 villes, ça fera 12 millions d'appels à la fonction RADIAN d'évités...

[Couscouss sensei]

Merci pour ton retour
Effectivement j'ai pas pensais a faire la jointure comme cela.
Je n'ai pas besoin de connaitre exactement le nombre de kilomètre séparant la ville de l'event avec les autres donc je pense que je peut me passer de la procédure

Il y a un influence de créer un index sur les les deux champs Latitude et Longitude ensemble ou séparer ?
Car j'avais déjà les index distinct

[aieeeuuuuu]

Il y a un influence de créer un index sur les les deux champs Latitude et Longitude ensemble ou séparer ?

Oui, si vous créez deux index séparés, le moteur devra chercher dans chaque index séparément, puis trouver les lignes en commun... ça pourrait le pousser à ne pas utiliser les index, jugeant que ce sera trop couteux...
Alors qu'avec un seul index, il a toutes les informations sous la main (latitude et longitude) pour vérifier les trois conditions.

Il pourrait même être intéressant d’ajouter d'autres colonnes afin que l'index soit couvrant, mais sur ce point je ne comprend pas bien votre requête : vous n'utilisez aucune colonne de V2, ni dans le select, ni dans le WHERE... du coup, je ne comprend pas trop l’intérêt de votre jointure avec V2...

D'ailleurs, je ne comprend pas non plus votre GROUP BY, qui est en plus incorrect.

[Couscouss sensei]

J'ai compris la nuance avec les index.

Concernant la requête c'était une parmi mes différents essais.
Voici la dernière (simplifié) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT V2.slug, V2.CP, E.date
FROM event E
INNER JOIN lieu L ON L.id = E.id_lieu
INNER JOIN villes V ON V.id = L.id_ville
INNER JOIN villes V2 ON (
  V.Latitude BETWEEN V2.Latitude - 0.3 AND V2.Latitude + 0.3
  AND V.Longitude BETWEEN V2.Longitude - 0.3 AND V2.Longitude + 0.3
)
WHERE E.date > CURDATE()
GROUP BY V2.slug, V2.CP

Le GROUP BY est la pour ne pas avoir de doublon de villes/cp

[aieeeuuuuu]

Si vous voulez éviter les doublons, utilisez distinct.

Pour cette dernière requête, vous pourriez ajouter les colonnes slug et CP à votre index pour qu'il couvre la requête.

Mais le mieux est de tester et de comparer !

[Couscouss sensei]

J'ai pris l'habitude de filtrer avec GROUP BY plutôt que DISTINCT car j'ai remarqué que quand il y a d'autred données dans le SELECT le DISTINCT ne filtre plus

J'avais également déjà un index sur les champs slug et cp que j'ai suite à votre message regroupé

[aieeeuuuuu]

En effet, DISTINCT ne renvoi que des lignes distinctes.

GROUP BY, utilisé comme vous le faites, renvoi des données non déterministes : la même requête exécutée deux fois de suite pourra vous renvoyer des résultats différents.

Dans le cas de votre dernière requête, elle renverra n'importe quelle date pour une ville donnée, et ce ne sera pas forcément toujours la même.

Vous devriez donc définir la date que vous voulez renvoyer dans ce cas, par exemple la plus proche et dans ce cas faire une MIN sur la date. Vous pouvez aussi renvoyer la plage de date (min et max) avec un COUNT pour connaitre le nombre d'événements dans la ville et sur quelle plage de dates.

bref, c'est à vous de voir, mais actuellement, votre requête, bien qu'acceptée par MYSQL, est fausse

[Couscouss sensei]

Oui j'en suis conscient

En tout cas merci pour votre aide !