MySQL en carton ?

**Aquellito** · 14/05/2013, 15h30

Bonjour à tous,

J'ai encore une requête qui met des plombes à s'executer et je ne comprends pas pourquoi.... Encore un problème de performance de MySQL, ou de ma requête ?

Ma requête va chercher dans un champ le 4ème et le 5ème caractères pour le comparer à un tableau comportant n lignes. (plus concrètement, ces caractères sont des codes postaux d'établissements qu'il faut afficher ou non en fonction des critères de recherche enregistrés dans le tableau).

Création de la requête en asp :

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
19
20
21
22
23
24
25
 
req = 	"SELECT up.libelle libelle_up, up.code_aru aru_up, up.actif_tonna, sj.libelle libelle_societe FROM unites_production up, societes sj, unites_exploitation ue "+
		"WHERE ue.code_aru = up.aru_ue "+
		"AND up.actif = 1 "+
		"AND up.actif_indus = 1 "+
		"AND up.aru_societe = sj.code_aru ";
if (aru_filiale != "")
	{
		req += "AND up.aru_filiale='"+aru_filiale+"' ";
	}
	if (code_type_up != "")
	{
		req += "AND up.code_type_up='"+code_type_up+"' ";
	}
	if (recherche != "")
	{
		req += "AND (up.libelle LIKE '%"+recherche+"%' OR sj.libelle LIKE '%"+recherche+"%') ";
	}
	tab_cp = str_cp.split(",");
	req += "AND (ue.code_aru LIKE '___"+tab_cp[0]+"_____' ";	
	for (i=1 ; i<tab_cp.length ; i++)
	{
		req += "OR ue.code_aru LIKE '___"+tab_cp[i]+"_____' ";
	}
	req += ") ORDER BY libelle_societe, libelle_up ";

Requête crée si je sélectionne tous les code postaux d'IDF:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT up.libelle libelle_up, up.code_aru aru_up, up.actif_tonna, sj.libelle libelle_societe 
FROM unites_production up, societes sj, unites_exploitation ue 
WHERE ue.code_aru = up.aru_ue 
AND up.actif = 1 AND up.actif_indus = 1 
AND up.aru_societe = sj.code_aru 
AND (ue.code_aru LIKE '___init_____' OR ue.code_aru LIKE '___95_____' 
OR ue.code_aru LIKE '___78_____' OR ue.code_aru LIKE '___91_____' 
OR ue.code_aru LIKE '___77_____' OR ue.code_aru LIKE '___92_____' 
OR ue.code_aru LIKE '___93_____' OR ue.code_aru LIKE '___94_____' 
OR ue.code_aru LIKE '___75_____' ) 
ORDER BY libelle_societe, libelle_up

En fait c'est la clause LIKE '___**_____' qui raaaaaaaaaame.... S'il n'y a qu'un département, ça prend déjà environ 3 secondes.... Si j'essaye avec l'exemple ci-dessus, la requête n'abouti pas et je suis obligé de l'interrompre....

Que faire ? à part brûler mon serveur MySQL

?

Merci beaucoup,
Axel

**punkoff** · 14/05/2013, 15h56

bonjour,

Même s'il est facile de tapper sur MySql, ici vous avez quand même un problème de modélisation.

Vous devriez avoir une table externe des codes postaux, qui comporterai des info supplémentaire, comme le numéro de département.

Ceci vous permettrai d'avoir plus de latitude dans vos possibilités de recherche ...

La avec un tel Like, MySql est forcé de faire un scannage de table.
(Peut être qu'il gère mal les OR imbriqué mais c'est une autre histoire... )

**Aquellito** · 14/05/2013, 16h22

Encore un problème de performance de MySQL, ou de ma requête ?

Je ne disais pas que c'était forcément MySQL, c'est pour ça que je me permet de poser la question

J'ai bien une table externe avec les codes postaux, mais celle-ci me sert uniquement à initialiser une carte de france cliquable qui sélectionne un certain nombre de codes postaux en fonction du/des départements sélectionnés.

Mais je ne vois pas comment cette table pourrait nous aider dans ce cas. Mais je me trompe peut-être ?

**punkoff** · 14/05/2013, 16h27

prenons une table des codes postaux :
CodePostale (CP, num_departement, libellé, ....)

(je prend le cp comme pk)

Si vous recherchez tous les codes postaux lié à un ou n département il sera très facile à votre SGBD de ressortir le(s) CP concerné(s).

Ensuite il lui sera aussi facile de retrouver les lignes concernées dans la table référencé via une jointure et .. un index.

Chose qu'il ne peut pas faire actuellement car votre recherche comporte un like => scannage de table systématique si la contrainte de recherche commence avec un _ ou %

Même dans le cas où vos critères de recherche forcerai le SGBD à réaliser un scannage de la table référencée, il le fera sur une colonne sans avoir à faire des transformations de donnée.

edit : bref c'est ce que je ferai pour être sur que ma modélisation tiens la route, sur le long terme, par rapport au besoin.

Maintenant si vous n'avez qu'une faible volumétrie, le problème viendra plutôt des capacités de votre serveur ou du mauvais traitement de la requête qu'autre chose.

Mettez au moins l'explain de la requête + volumétrie.

**Aquellito** · 14/05/2013, 16h48

Je dois être un peu blond... je ne comprends pas vraiment ce que tu veux faire...

Code :

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

Si vous recherchez tous les codes postaux lié à un ou n département il sera très facile à votre SGBD de ressortir le(s) CP concerné(s)

Je n'ai pas de problème pour sortir les CP en fonctions des départements, mon problème est de sortir une liste d'établissements en fonctions des CP sélectionnés.

Le problème est que dans ma table UE, c'est le champ 'code_aru' qui comporte le CP à vérifier. il est toujours sous cette forme xxxCPxxxxx. Comment vérifier ces deux caractères sans passer par la clause LIKE ?

PS : ci-dessous le EXPLAIN

**punkoff** · 14/05/2013, 16h58

En externalisant cette donnée dans une colonne (d'où ma remarque sur le problème de modélisation, une unité de production dépend d'un code postale d’après ce que je comprend)

Ou en trouvant le cp de l'unité de production d'une autre manière (présent dans une autre table ?)

Ici vous avez plusieurs informations dans une même colonne.

Votre code d'unité de production est surement référencé autre part non ?

**Aquellito** · 14/05/2013, 17h06

C'est vrai que je ne suis pas très clair... dsl

La table unites_production up dépend de la table table unites_exploitation ue qui a le champ ue.code_aru qui comporte le CP à vérifier sur les 4ème et 5ème caractères. Mais ce sont bien les données de la table unites_production que je veux afficher. La table unites_exploitation ne me sert qu'à filtrer en fonction des CP sélectionnés dans l'appli.

**aieeeuuuuu** · 14/05/2013, 17h50

Envoyé par Aquellito

Comment vérifier ces deux caractères sans passer par la clause LIKE ?

Vous pouvez essayer de remplacer votre série de LIKE... OR par un

Code :

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

1
2
 
SUBSTRING(ue.code_aru , 5, 2) IN ('init', '95','78','91',...)

Mais punkoff a raison : le problème vient de votre modèle...

**Aquellito** · 14/05/2013, 17h56

Le problème est que je dois faire avec ce modèle qui est celui de la société dans laquelle je travail.

Je vais essayer la méthode SUBSTRING, ça me semble être une bonne piste.

**Aquellito** · 14/05/2013, 18h31

Je viens de tester, c'est trois fois plus rapide. Donc 1 minute au lieu de 3 minutes

C'est compliqué cette histoire...

**Fred_34** · 15/05/2013, 10h21

J'ai l'impression que tu n'as pas besoin de ta jointure avec unites_exploitation, tu dois pouvoir utiliser directement up.aru_ue :

Code :

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

SUBSTRING(up.aru_ue , 5, 2) IN ('init', '95','78','91',...)

**Aquellito** · 15/05/2013, 10h50

Oui tu as raison, j'utilise la table UE pour rien. Merci pour ta remarque

Par contre dans notre cas, cela n'améliore pas beaucoup le temps d'exe de la requête.

Du coup je suis en train de modifier mon moteur de synchro pour qu'il y ait un champ CP à 2 caractères seulement. C'est justement ce que je ne voulais pas faire vu qu'on a l'info dans un autre champ mais bon.... c'est moche

Je ne met pas résolu parce que pour moi là c'est plus de la bidouille qu'autre chose...

**punkoff** · 15/05/2013, 11h25

Votre code aru est composé de combien d'information différentes ?

Edit :
Perso je ne suis pas expert en modélisation, par contre il y a des règles basiques : http://fsmrel.developpez.com/basesre...sation/?page=2

Ici vous voulez exploiter une donnée dé-normalisée.
Et vous vous heurtez à un problème typique de cassage de la 1nf.

Vous avez plusieurs information regroupez dans un même attribut et le SGBD ne suis pas à cause de ça.

Certain SGBD propose des paliatif comme par exemple pouvoir créer un index avec une fonction (ici le substring), mais le problème reste le même : la bidouille c'est la mauvaise normalisation de votre base.

Typiquement votre code Aru devrait être splité sur plusieurs colonnes, afin de bien différencier chaque information.

Par exemple prenons un numéro de sécurité sociale, si vous modélisé ce code en 1 seule colonne, je vous souhaite bonne chance pour retrouver les personnes de sexe féminin née à paris en 1965.

en découpant proprement votre colonne de numéro de sécurité sociale, répondre à cette problématique devient triviale... pour vous et pour votre SGBD.

**Aquellito** · 15/05/2013, 15h03

Merci pour le lien que vais m'empresser de lire dès que j'ai un moment.

Le code aru est une clé qui permet à un moteur de synchro d'armoniser ma structure avec celle du Groupe ou je bosse. Je suis donc obligé de garder ce code.
Elle comporte 3 informations (ou plutot 4), mais il n'y en a qu'une qui m'intéresse :

xxx11111yy

les 5 chiffres du milieu de la chaine représentent le code INSEE.
Et ce sont les deux premiers caractères de ces chiffres (qui représente le département), qui m'intéressent pour pouvoir filtrer par département.

J'ai donc maintenant 2 champs : 1 pour le code aru et 1 pour le département.

Donc apparemment ce n'est pas une bidouille ce que j'ai fait...

Merci à tous

**Rei Ichido** · 15/05/2013, 15h10

Envoyé par Fred_34

J'ai l'impression que tu n'as pas besoin de ta jointure avec unites_exploitation, tu dois pouvoir utiliser directement up.aru_ue :

Code :

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

SUBSTRING(up.aru_ue , 5, 2) IN ('init', '95','78','91',...)

Juste une remarque : le substring considéré, sur 2 caractères, ne va pas bien aller avec la valeur 'init'

{à moins que MySQL ne tronque volontairement, ce qui serait vraiment bizarre}

**Aquellito** · 15/05/2013, 15h15

Oui dsl j'ai changé ça dans le code mais je ne l'ai pas reposté ici. J'ai mis '00' à la place. Cette valeur ne me sert qu'à avoir une condition impossible pour ne pas commencer par une virgule dans ma boucle lors de la création de la requête...

**Rei Ichido** · 15/05/2013, 15h41

Ah, dans ce cas 'init' n'est pas gênant comme valeur, si le but c'est d'avoir une valeur impossible !
Mais ne vaudrait-il mieux pas gérer à la source cette condition pour éviter d'envoyer une valeur inutile au SGBD ?

**skuatamad** · 15/05/2013, 16h34

Utilise plutôt 0 = 1 comme condition impossible (et son pendant 1 = 1 pour les AND)
Le SGBD n'a pas à lire de données pour se rendre compte que c'est toujours faux (ou toujours vrai).

**Aquellito** · 15/05/2013, 17h09

Ok merci pour l'info.

Bon je suis un peu déçu le fait de vérifier sur un champ séparé le département n'améliore pas vraiment les performances... Je ne sais plus trop quoi faire...
Je vais faire comme avant, des requête imbriquées à tout va. C'était beaucoup plus rapide j'ai l'impression...

**Rei Ichido** · 15/05/2013, 17h23

Il faudrait indexer cette colonne pour que cela soit vraiment intéressant.
Idéalement, avec un index qui utilise aussi les autres colonnes utilisées dans les prédicats de la requête (selon le degré avec lequel ils filtrent effectivement les lignes de la table).

Par exemple, ici, on pourrait facilement se laisser tenter par l'index :
CREATE INDEX idx_test ON unites_exploitation (aru_societe_departement,code_aru)
Pour faire la jointure après avoir utilisé le critère sur aru_societe sans repasser par la table.

EDIT : ou si on fait sauter la jointure et que le renseignement est directement dans la table unites_production :
CREATE INDEX idx_test_2 ON unites_production (aru_ue_departement ,actif ,actif_indus)

EDIT bis : modification du nom des colonnes pour indiquer qu'il s'agit de la partie département seulement.

MySQL en carton ?

Langage SQL

Discussions similaires

Partager

Partager