Optimisation d'une requete avec jointure [Résolu]

[labyala]

Bonjour,

la situation :
j'ai 2 tables qui enregistre des produits et leurs propriétés : cat_product(id_product,libelle) et cat_product_additionnal_properties(id_product,id_champs,value,lang)
chaque produit à environ 10 à 20 propriétés additionnelles.
La table cat_product_famille enregistre 1 produit dans 1 famille.

La table cat_product contient 20 000 lignes et la table cat_product_additionnal_properties contient 320 000 lignes.

Je souhaite afficher sur une page tous les produits d'une famille avec leurs propriétés, le tout listé dans un tableau, mais je me heurte à des temps d'execution de ma requete trop élevé lorsqu'il y a plus de 400 produits dans une famille.

Et maintenant voici un exemple de requête, cet exemple est particulier car pour cette famille (qui contient 12 000 produits) je dois grouper mes résultats par marque et modèle, qui sont 2 propriétés additionnelles du produit, pour éviter d'afficher 12 000 produits (le group by réduit a 800)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT cat_product.id_product, i18n.traduction_fr, addpropMarque.value AS marque,addpropModele.value AS modele 
FROM cat_product_famille 
LEFT JOIN cat_product ON cat_product_famille.id_product=cat_product.id_product 
LEFT JOIN i18n ON i18n.shortcut=cat_product.libelle 
LEFT JOIN cat_product_additionnal_properties addpropMarque ON addpropMarque.id_product=cat_product.id_product 
LEFT JOIN cat_product_additionnal_properties addpropModele ON addpropModele.id_product=cat_product.id_product 
WHERE addpropMarque.id_champs=133 AND 
addpropMarque.lang="fr" AND 
addpropModele.id_champs=59 AND 
addpropModele.lang="fr" AND 
cat_product_famille.id_famille=156 
GROUP BY marque,modele

Actuellement cette requete dépasse le délai d'execution. Je cherche donc un moyen d'optimiser cette requete. Est-ce que la logique de ma requête est mauvaise ? Est-ce qu'il y a un moyen d'eviter les jointures ?...
Merci par avance pour votre aide.

(Remarque: je bosse sur un CMS payant, la structure de la bdd n'est pas modifiable)

[punkoff]

Bonjour,


Déjà votre group by est mal écrit, en effet vous n'avez que 2 colonnes sur les 4 du select qui sont spécifiés dans la clause group by.

Ensuite si vous n'utilisez pas de fonction d'agrégation (count, min, etc) c'est un distinct qu'il faut utiliser.


Point suivant, vous avez 3 left outer join qui vont être transformés en inner join car vous mettez dans la clause where des restrictions sur ces tables : utilité des left outer join ?


Mise à part ça, il faudrait vérifier que vous avez des index sur toutes les clauses de jointure.

Quand tout ceci sera vérifier et si c'est toujours lent il faudra sans doute présenter l'explain de la requête pour qu'une personne puisse l'analiser et proposer une solution (ou pas c'est peut-être le temps de fetch du curseur qui est lent)

[CinePhil]

1) GROUP BY s'utilise avec des fonctions de regroupement (COUNT, SUM, AVG, MIN, MAX).
Du fait de ton GROUP BY, id_product et traduction_fr seront aléatoires.

2) Mettre une condition de restriction dans le WHERE sur une table externe d'une jointure externe revient à faire une jointure interne.
Voir mon blog pour plus d'explication.

En l'occurence, je crois que des jointures internes seraient ici suffisantes non ?

3) Si ta requête est longue, commence par vérifier si les tables sont bien indexées, notamment sur les colonnes figurant dans les conditions de jointure. La plupart sont des id donc en principe des clés primaires et étrangères et il y a de fortes chances qu'elles soient indexées.
Par contre, la table i18n est plus inquiétante ! Une jointure sur un libellé est contre performant.

4) L'utilisation systématique d'alias courts rend la lecture de la requête plus facile.

Dis-nous plus précisément ce que tu cherches à obtenir parce que ton GROUP BY est incohérent.

EDIT : Grilled by Punkoff !

[labyala]

bonjour,

merci à vous 2 pour vos réponses rapides, je reviens sur mon problèmes (après un passage sur un autre projet + les vacances qui sont passées par là).

J'ai pris note de vos précieuses remarques sur le group by et le left join.
J'ai refait quelques tests aujourd'hui en simplifiant ma requête

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT cat_product.id_product, addpropMarque.value AS marque, addpropModele.value AS modele, addpropVue.value AS vue
FROM cat_product_famille 
INNER JOIN cat_product ON cat_product_famille.id_product=cat_product.id_product 
INNER JOIN i18n ON i18n.shortcut=cat_product.libelle 
INNER JOIN cat_product_additionnal_properties addpropMarque ON addpropMarque.id_product=cat_product.id_product 
INNER JOIN cat_product_additionnal_properties addpropModele ON addpropModele.id_product=cat_product.id_product 
INNER JOIN cat_product_additionnal_properties addpropVue ON addpropVue.id_product=cat_product.id_product 
WHERE 
addpropMarque.id_champs=133 AND addpropMarque.lang="fr" 
AND addpropModele.id_champs=59 AND addpropModele.lang="fr" 
AND addpropVue.id_champs=132 AND addpropVue.lang="fr" 
AND cat_product_famille.id_famille=156

cette requete doit me resortir 14 976 lignes mais le délai d'execution est beaucoup trop long (si je fais qu'une seule jointure sur la table cat_product_additionnal_properties il n'y a pas de pb)

Les index sont bien créé sur les différents champs de jointure (même sur i18n.shortcut)

voici le explain de la requete :

Code :

1
2
3
4
5
6
7
id 	select_type 	TABLE 	type 	possible_keys 	KEY 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	addpropMarque 	ALL 	NULL 	NULL 	NULL 	NULL 	320206 	USING WHERE
1 	SIMPLE 	cat_product 	eq_ref 	PRIMARY,id_product 	PRIMARY 	4 	dbsymtapiecess3030com.addpropMarque.id_product 	1 	 
1 	SIMPLE 	i18n 	ref 	shortcut 	shortcut 	767 	dbsymtapiecess3030com.cat_product.libelle 	1 	USING INDEX
1 	SIMPLE 	cat_product_famille 	ref 	id_product,id_famille 	id_product 	4 	dbsymtapiecess3030com.addpropMarque.id_product 	1 	USING WHERE
1 	SIMPLE 	addpropModele 	ALL 	NULL 	NULL 	NULL 	NULL 	320206 	USING WHERE
1 	SIMPLE 	addpropVue 	ALL 	NULL 	NULL 	NULL 	NULL 	320206 	USING WHERE

[punkoff]

Que veut dire long ?

Sinon que donne cette requête ?

edit :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT cat_product.id_product, 
max(case when addprop.id_champs = 133 then addprop.value end) AS marque, 
max(case when addprop.id_champs = 59 then addprop.value end) AS modele, 
max(case when addprop.id_champs = 132 then addprop.value end) AS vue
FROM cat_product_famille 
INNER JOIN cat_product ON cat_product_famille.id_product=cat_product.id_product 
INNER JOIN i18n ON i18n.shortcut=cat_product.libelle 
INNER JOIN cat_product_additionnal_properties addprop ON addprop.id_product=cat_product.id_product 
WHERE 
addprop.id_champs IN (133, 59, 132) AND addprop.lang="fr" 
AND cat_product_famille.id_famille = 156 
GROUP BY cat_product.id_product
HAVING count(DISTINCT addprop.id_champs) = 3

edit2 : Sinon essayez aussi avec un de ces deux indexs :

Code :

1
2
3
 
CREATE INDEX idx_addprop_1 ON cat_product_additionnal_properties (lang, id_champs, id_product);
CREATE INDEX idx_addprop_2 ON cat_product_additionnal_properties (id_champs, lang, id_product);

[labyala]

long ça veut dire que la requête dépasse le délai d’exécution.

ta requête est très efficace, délai de 5 à 6 secondes. Je n'aurais jamais pensé l'écrire de cette manière !

par contre je ne comprends pas le

Code :

HAVING count(DISTINCT addprop.id_champs) = 3

peux-tu m'expliquer ?

merci

[CinePhil]

Si on ne met pas le HAVING, du fait de WHERE addprop.id_champs IN (133, 59, 132), les lignes ayant l'un des trois critères seraient pris en compte. Le HAVING permet de dire qu'on ne prend que les cat_product.id_product ayant 3 adddrop.id_champ différents, c'est à dire les 3 qui sont dans le IN.

[labyala]

ok c'est plus clair pour moi merci

je me rapproche du but toutefois une dernière question : est-ce qu'il est possible avec cette requete de filtrer sur une valeur présente dans addprop.value ? Par exemple si je ne veux retourner que les enregistrements d'une seule marque ?
J'ai fait plusieurs tests mais je n'y parviens pas et je ne vois pas comment faire ?

[CinePhil]

La marque est stockée où ?
Quelle requête as-tu essayé ?

[labyala]

je parle de la requête de punkoff

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT cat_product.id_product, 
max(case when addprop.id_champs = 133 then addprop.value end) AS marque, 
max(case when addprop.id_champs = 59 then addprop.value end) AS modele, 
max(case when addprop.id_champs = 132 then addprop.value end) AS vue
FROM cat_product_famille 
INNER JOIN cat_product ON cat_product_famille.id_product=cat_product.id_product 
INNER JOIN i18n ON i18n.shortcut=cat_product.libelle 
INNER JOIN cat_product_additionnal_properties addprop ON addprop.id_product=cat_product.id_product 
WHERE 
addprop.id_champs IN (133, 59, 132) AND addprop.lang="fr" 
AND cat_product_famille.id_famille = 156 
GROUP BY cat_product.id_product
HAVING count(DISTINCT addprop.id_champs) = 3

[CinePhil]

Ok je n'avais pas bien lu la question.

est-ce qu'il est possible avec cette requete de filtrer sur une valeur présente dans addprop.value ? Par exemple si je ne veux retourner que les enregistrements d'une seule marque ?

Oui mais à condition de l'associer avec une valeur de adddrop.id_champ.

Quand je demandais ceci :

Envoyé par CinéPhil

Quelle requête as-tu essayé ?

Je faisais référence à cela :

Envoyé par labyala

J'ai fait plusieurs tests mais je n'y parviens pas et je ne vois pas comment faire ?

Si tu ne veux qu'une seule marque, le comptage sur la marque devient inutile.
On sort la marque (code 133) du IN et on lui concocte une condition sur mesure :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT cat_product.id_product, 
max(case when addprop.id_champs = 59 then addprop.value end) AS modele, 
max(case when addprop.id_champs = 132 then addprop.value end) AS vue
FROM cat_product_famille 
INNER JOIN cat_product ON cat_product_famille.id_product=cat_product.id_product 
INNER JOIN i18n ON i18n.shortcut=cat_product.libelle 
INNER JOIN cat_product_additionnal_properties addprop ON addprop.id_product=cat_product.id_product 
WHERE addprop.lang="fr" 
	AND cat_product_famille.id_famille = 156 
	AND
	(
		(
			addprop.id_champs = 133
			AND addprop.value = 'la marque'
		)
		OR addprop.id_champs IN (59, 132) 
	)
GROUP BY cat_product.id_product
HAVING count(DISTINCT addprop.id_champs) = 3

[labyala]

un grand merci à vous 2 !
grâce à votre aide je vais pouvoir avancer comme je le souhaite.