Discussion :

[krunch]

Bonjour
J'essaie d'optimiser une requête SQL appelée un grand nombre de fois par un script :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
// Renvoie la distance de la ville de nom $new la plus proche des latitude/longitude de $coo(['lat'],['lon'])
function Get_Distance ($bdd, $coo, $new)
{
	$coo['ville'] = $new;
	$req = $bdd->prepare ('SELECT 6371 * ACOS (SIN (RADIANS (latitude)) * SIN(:lat) + COS (RADIANS (latitude)) * COS (:lat) * COS (RADIANS (longitude) - :lon)) D FROM `fr` WHERE :ville LIKE VILLE ORDER BY D ASC LIMIT 1');
	$req->execute($coo);
	// Restitution 1ère donnée
	$out = $req->fetch(PDO::FETCH_ASSOC);
	$req->closeCursor();
	return intval($out['D']);
}

1- Les coordonnées $coo sont converties en radians par php mais dans la table elles sont en d°
Pour le 'RADIANS (latitude)' répété 2 fois on ne peut pas écrire 'COS (RADIANS (latitude) AS L)' au milieu du calcul, faut-il le pré-déclarer dans le select même si la valeur n'est pas renvoyée ?

2- Le résultat est une chaine et non un nombre (la partie entière me suffit d'où le 'intval')

3- Si c'était un nombre je suppose que MIN pourrait être utilisé à la place de 'ORDER BY D ASC LIMIT 1' (marche pas) ?

Enfin de façon générale faut-il partir du principe que SQL est plus rapide que php ??

Merci d'avance

[Fred_34]

Je suppose qu'à chaque appel, il n'y a pas 50 villes qui ont le même nom. Le calcul n'étant pas fait un grand nombre de fois, son coût est probablement négligeable par rapport au coût de la requête.
De même pour le ORDER BY, vu qu'il n'y a pas grand chose à trier, ce n'est probablement pas la peine de s'y attarder.


Reste le :

Code :

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

WHERE :ville LIKE VILLE

Tu pourrais mettre "=" et as tu bien indexé VILLE ?

[krunch]

En fait c'est toute la fonction qui appelée un grand nombre de fois (elle est dans une boucle) ... avec des noms test $new différents.
Sinon, la probabilité de doublons ('VILLE') est effectivement très faible.

A part ça le LIKE gère les accents. Les noms 'VILLE' sont directement codés en utf8 (Périgueux = Périgueux) mais je ne sais pas si c'est la meilleure solution. Je l'ai récupéré comme ça (chez geonames), et à vrai dire je n'ai pas encore géré le problème des accents de façon plus poussée.

Et quant à l'indexation de la colonne j'avoue ne pas non plus avoir étudié le truc .. Je précise que la table n'est pas très volumineuse (38000 entrées) et qu'elle a une clé primaire.
Elle sert également à une autocompletion. Si il y a un vrai bénéfice à indexer la colonne, j'indexe ..

[Fred_34]

Il va te falloir 2 minutes pour ajouter un index, et 2 minutes pour l'enlever si les résultats ne sont pas concluants. Ça vaut le coup d'essayer...

Je peux comprendre l'utilité d'une telle fonction, mais je n'arrive pas à comprendre son intérêt lorsqu'elle est dans une boucle.

[krunch]

Ok, je regarderai l'indexation de plus près, également si ça m'aide à gérer les accents.

Je peux comprendre l'utilité d'une telle fonction, mais je n'arrive pas à comprendre son intérêt lorsqu'elle est dans une boucle.

C'est parce que le nom des villes 'test' ($new) ne viennent pas de la base, il proviennent d'un parsing. Je fais un calcul de distance (avec la ville de la requête initiale) sur chaque résultat (différent) pour filtrer/trier...

[Fred_34]

Au lieu d'une boucle, tu pourrais faire une seule requête de ce genre :

Code :

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

1
2
3
4
SELECT VILLE, MIN(calculdistance) AS D 
FROM `fr` 
WHERE VILLE IN (ville1,ville2,ville3,...) # Ou VILLE LIKE ville1 OR VILLE like ville2 OR ...
GROUP BY VILLE

[krunch]

Pourquoi pas, mais dans le parsing je fabrique un tableau de tableaux...
Alors ça m'obligerait à ajouter d'autres boucles : une pour extraire les noms des villes des tableaux, puis une autre pour insérer la distance dans le tableau de la ville correspondante (avec un test).. Je suis pas sûr que ça optimise.

A propos de MIN c'était une de mes questions : la requête suivante ne renvoie rien ..

Code :

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

SELECT MIN (6371 * ACOS (SIN (RADIANS (latitude)) * SIN(:lat) + COS (RADIANS (latitude)) * COS (:lat) * COS (RADIANS (longitude) - :lon))) FROM `fr` WHERE :ville LIKE VILLE

[apaul]

A propos de MIN c'était une de mes questions : la requête suivante ne renvoie rien ..

Code :

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

SELECT MIN (6371 * ACOS (SIN (RADIANS (latitude)) * SIN(:lat) + COS (RADIANS (latitude)) * COS (:lat) * COS (RADIANS (longitude) - :lon))) FROM `fr` WHERE :ville LIKE VILLE

Je ne sais pas si c'est une typo, mais le LIKE, il veut des quotes et il fonctionne dans le sens inverse:

Code :

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

WHERE ville LIKE 'pari%'

[krunch]

Désolé, en fait le MIN marche très bien (avec LIKE dans les 2 sens) et permet de remplacer le 'ORDER BY D ASC LIMIT 1'.
Version simplifiée :

Code :

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

1
2
3
4
5
6
$coo['ville'] = $new;
$req = $bdd->prepare ('SELECT RADIANS (latitude) L, MIN(6371 * ACOS (SIN (L) * SIN(:lat) + COS (L) * COS (:lat) * COS (RADIANS (longitude) - :lon))) D FROM `fr` WHERE :ville LIKE VILLE');
$req->execute($coo);
$out = $req->fetch();
$req->closeCursor();
return $out['D'];

Pour ce qui est de la structure, Fred m'a mis un doute ...
J'ai fait la structure la plus évidente avec cette fonction appelée dans la boucle principale, comme si c'était une fonction de calcul normale.
Dans la mesure où c'est une requête SQL ce n'est peut-être pas le bon raisonnement, je ne sais pas ..

[ericd69]

salut,

et si tu utilisais une fonction stockée pour la formule, ça te permettrait au moins de simplifier l'écriture de ce genre de requête, de limiter la taille de code à transférer à mysql et si tu appelles la fonction plusieurs fois dans la même session tu as en plus le gain de la compilation et du plan d'exécution...

[krunch]

Salut

J'ai jeté un œil aux procédures stockées .. effectivement c'est pas bête (c'est un peu comme définir une méthode) et probablement plus rapide.
Si je reste sur cette structure je l'adopte.

En gros j'ai le choix entre :
- cette structure qui répète la requête SQL (mettons de 10 à 300 fois par requête, en procédure stockée)
- une structure php plus compliquée qui fait 1 seule requête SQL (plus compliquée aussi)

Je ne sais pas si la 1ère solution (plus simple) est un handicap pour un serveur normal, si quelqu'un sait surtout qu'il n'hésite pas ..

Et sinon merci pour vos réponses.

[Fred_34]

300 requêtes c'est souvent plus handicapant qu'une seule requête...

As-tu ajouté un index ?

[ericd69]

c'est surtout un gain dans l'écriture si ce calcul entre dans plusieurs requête...

de toutes façons, je recommande toujours de séparer langage appelant du sql en utilisant du procédural...

ça renforce encore la sécurité et l'isolation et la maintenance...

en général, pour les problème de distance à des coordonnées (ou une autre ville de référence) tu fais ton calcul de distance par rapport à chaque ville que tu classes en ordre desc sur la distance avec un "limit 1" et tu as ton résultat...

pas besoin de traitement externe à mysql...

[krunch]

As-tu ajouté un index ?

Oui j'ai essayé d'indexer la colonne 'VILLE' mais ça ne fait pas de différence : en local, avec ou sans index la requête globale dure env 400ms pour une 20aine de requêtes SQL (et 20 si aucune ..), par contre ça augmente la taille de la table de 1/5.

tu fais ton calcul de distance par rapport à chaque ville que tu classes en ordre desc sur la distance avec un "limit 1" et tu as ton résultat...

C'est ce que je fais dans cette fonction (avec MIN).
J’essaierai en procédure stockée, ça m'a l'air effectivement plus pertinent (également pour l'autocompletion d'ailleurs).


Pour ce qui est de la structure je ne chasse pas les millisecondes, c'est juste histoire de ne pas partir sur un truc foireux ..

[ericd69]

je voulais te faire comprendre que c'est le genre de traitement (boucle ou autre truc) que tu n'as pas forcément à faire coté langage appelant...

une fois tes données chargées en bd tu peux faire plein de choses en procédural en bd et éviter les allers-retours avec le langage appelant

[krunch]

je voulais te faire comprendre que c'est le genre de traitement (boucle ou autre truc) que tu n'as pas forcément à faire coté langage appelant...

Oui en fait vous avez tous les deux raisons, je m'accrochais à une structure lourdingue (chaque appel SQL depuis mon parsing prend 20ms, soit autant que le parsing complet sans appel).

Alors j'ai fait un essai avec le code de Fred (message #6) écrit sur mesure, il gère les éventuels doublons de nom de villes en renvoyant la plus proche .. il marche impeccable !

Ça m'oblige à réécrire mes parsings pour extraire les noms des villes dans un tableau à part, puis à les refusionner .. mais c'est sans doute une meilleure solution.
Ça donnerait quelque chose comme ça :

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
function Get_Distance2 ($bdd, $coo, $new)
{
	$req = "SELECT VILLE, MIN(6371 * ACOS (SIN (RADIANS (latitude)) * SIN(:lat) + COS (RADIANS (latitude)) * COS (:lat) * COS (RADIANS (longitude) - :lon))) AS D 
		FROM `fr` 
		WHERE VILLE IN ('".implode("','",$new).'\')
		GROUP BY VILLE';
 
	$req = $bdd->prepare ($req);
	$req->execute($coo);
 
	while ($donnees = $req->fetch())
	{
		echo $donnees['VILLE'].$donnees['D'].'<br />';
	}
	$req->closeCursor();
}

Avec

Code :

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

$new = array ('Paris', 'Lyon', 'Marseille')

Et une syntaxe de guillemets toute bizarre trouvée ici (apostrophes interdites dans les noms ..)

Bref, merci de m'avoir fait comprendre...