Simplifier requêtes avec jointures

**bibos** · 06/02/2012, 14h01

Bonjour à tous,

Je tâtonne avec l'utilisation des jointures.

Et aujourd'hui je cherche à simplifier deux requêtes que j'utilise.

Voilà mon cas :

- J'ai une table "articles"
- une table "categories"
- une table "catart" (qui lie les articles aux catégories - un article peut appartenir à plusieurs catégories : deux colonnes : idcat | idart)

Je dois sortir un listing d'articles avec différents champs, notamment toutes les catégories dans lesquelles l'article apparait.
J'ai plusieurs types dans ma table categories :

- marques (idcat=10)
- accessoires (idcat=12)
- telephones (idcat=11)

Pour compliquer l'affaire, ma catégorie "telephones" est elle même liée à la catégorie "marques" : Exemple: un téléphone "N86" est de marque "Nokia" (dans ma table le "N86" a donc pour "idcat" = 11 et pour "idcat2" = 10)

Et moi je dois sortir :

Article1 | Batterie | Nokia | N86
Article1 | Batterie | HTC | Legend
Article2 | Coque | Samsung | Galaxy SII
etc.

A l'heure actuelle j'utilise donc deux requêtes sans doute archaïques, mais qui ont le mérite de fonctionner... mais qui me prennent un max de ressources et de temps d'exécution.

Je fais donc d'abord :

Code :

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

1
2
3
4
5
6
7
SELECT A.id, A.denom as ARTICLE, B.denom as TELEPHONE, D.denom as MARQUE
FROM afs_article A 
LEFT JOIN afs_catart C ON A.id=C.idart 
LEFT JOIN afs_categorie B ON B.id=C.idcat 
LEFT JOIN afs_categorie D ON B.idcat2=D.id
WHERE B.idcat=11
ORDER BY ARTICLE

Mais je dois encore aller chercher ma catégorie "accessoires", et c'est pour elle que je butte... Donc pour le moment j'ai cette solution - de bourrin - de lancer ma boucle sur ma première requête, et d'aller pour chaque ligne chercher le type d'accessoires d'appartenance :

Code :

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

1
2
3
4
SELECT K.id as ACCESSOIRES 
FROM afs_catart J 
LEFT JOIN afs_categorie K ON K.id=J.idcat 
WHERE J.idart=$IDARTICLE AND K.idcat=12

Et donc ma question est évidemment : comment faire ça en une seule requête.

J'espère que j'ai été assez clair et pas trop long !

Merci d'avance pour toute aide !

**punkoff** · 06/02/2012, 14h20

bonjour,

Il faut remonter votre condition dans la clause where, sinon vous allez forcer des jointures internes.

Essayez ceci :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT A.id, A.denom as ARTICLE, B.denom as TELEPHONE, D.denom as MARQUE, e.denom as accessoire
FROM afs_article A 
LEFT JOIN afs_catart C ON A.id=C.idart 
LEFT JOIN afs_categorie B ON B.id=C.idcat and  B.idcat=11 
LEFT JOIN afs_categorie D ON B.idcat2=D.id
left outer join afs_categorie e on e.id = c.idcat and e.idcat = 12
ORDER BY ARTICLE

Concernant les perf, cette requête ramène beacoup d'enregistrement ?
Pour la table afs_catart je déduis que la primary key est le couple {idart, idcat} ?
Est-ce que votre colonne idcat2 de la table afs_categorie est indéxé ?

**CinePhil** · 06/02/2012, 14h22

Dans ta première requête, tu fais des jointures externes mais tu mets une condition dans le WHERE qui porte sur une table externe, transformant ainsi la jointure externe en jointure interne.

Lire mon billet de blog pour plus d'explications.

Par contre j'ai du mal à comprendre ton besoin et à deviner la structure de tes tables au vu de la requête.

Tu peux fournir ce qui manque pour qu'on puisse t'aider plus efficacement ?

**bibos** · 08/02/2012, 09h03

La vache vous êtes rapide à répondre ! C'est moi qui suis à la bourre du coup !

Donc pour ta requête punkoff, ça me donne une liste trop longue (130 articles au lieu de 125 000), avec pas mal de champs NULL :

ID | Article | Telephone | Marque | Accessoire
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	NULL 	NULL 	NULL
1000 	Bluetooth Mono sans fil HTC 	Z6 	MOTOROLA 	NULL
1000 	Bluetooth Mono sans fil HTC 	Z3 	MOTOROLA 	NULL

"Bluetooth Mono sans fil HTC" c'est le nom de l'article.
Et surtout je n'ai jamais à la fois le téléphone, la marque et l'accessoire sur la même ligne.

Oui c'est bien le couple id_art/id_cat le primary key sur afs_catart, et ma colonne idcat2 sur afs_categorie n'est pas indexée...

**punkoff** · 08/02/2012, 09h17

fournissez un jeu de donnée test + résutlat attendu + structure comme le suggère CinePhil

**bibos** · 08/02/2012, 09h29

Envoyé par punkoff

fournissez un jeu de donnée test + résutlat attendu + structure comme le suggère CinePhil

Les quelques lignes que j'ai donné ci-dessus suffisent ou non ?
Pour les structures de tables, vous avez besoin de quoi d'autre ?

Pour le résultat attendu, et bien ce serait ça :

id ARTICLE TELEPHONE MARQUE ACCESSOIRE
1000 Bluetooth Mono sans fil HTC T4242 HTC Bluetooth
1000 Bluetooth Mono sans fil HTC 6280 BLACKBERRY Bluetooth
1000 Bluetooth Mono sans fil HTC E60 NOKIA Bluetooth
1000 Bluetooth Mono sans fil HTC L600 SAMSUNG Bluetooth
(...)
69 	Adaptateur allume cigare HTC 	P3300 	HTC 	Divers
69 	Adaptateur allume cigare HTC 	MAX 4G 	HTC 	Divers
69 	Adaptateur allume cigare HTC 	TYTN II 	HTC 	Divers
90 	Adaptateur allume cigare Nokia 	6310 	NOKIA 	Divers
89 	Adaptateur allume cigare Nokia 	X3 	NOKIA 	Divers

**bibos** · 08/02/2012, 09h38

Si ça peut simplifier les choses, je n'ai pas forcément besoin du DENOM de la catégorie ACCESSOIRE.
Je peux récupérer simplement son ID, dans la table afs_catart.

Mais bon... je sais pas si ça simplifie les choses...

**punkoff** · 08/02/2012, 09h41

Envoyé par bibos

Les quelques lignes que j'ai donné ci-dessus suffisent ou non ?
Pour les structures de tables, vous avez besoin de quoi d'autre ?

Non, pas suffisant, car votre résultat est incohérant par rapport à votre présentation.

Dans votre 1er poste, j'ai simplement repris votre 2eme requête pour la joindre avec votre 1ere requête.

Donc si la requête que je vous ai posté ne retourne que des valeurs null pour votre accessoire cela veut dire que votre 2eme requête initial ne renvoie rien.

**bibos** · 08/02/2012, 09h22

Envoyé par CinePhil

Dans ta première requête, tu fais des jointures externes mais tu mets une condition dans le WHERE qui porte sur une table externe, transformant ainsi la jointure externe en jointure interne.

Lire mon billet de blog pour plus d'explications.

Par contre j'ai du mal à comprendre ton besoin et à deviner la structure de tes tables au vu de la requête.

Tu peux fournir ce qui manque pour qu'on puisse t'aider plus efficacement ?

Ok donc ma requête revient à faire :

Code :

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

1
2
3
4
5
6
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE
FROM afs_article A
LEFT JOIN afs_catart C ON A.id = C.idart
INNER JOIN afs_categorie B ON B.id = C.idcat AND B.idcat =11
LEFT JOIN afs_categorie D ON B.idcat2 = D.id
ORDER BY ARTICLE

Mais après je bloque toujours...

J'ai essayé d'ajouter la ligne de punkoff, ce qui me donne le bon nombre de lignes, sans champs NULL... mais avec toujours le même type d'accessoire remonté !

Code :

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

1
2
3
4
5
6
7
8
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, E.denom as ACCESSOIRE
FROM afs_article A
LEFT JOIN afs_catart C ON A.id = C.idart
INNER JOIN afs_categorie B ON B.id = C.idcat
AND B.idcat =11
LEFT JOIN afs_categorie D ON B.idcat2 = D.id
LEFT OUTER JOIN afs_categorie E ON E.id = E.idcat AND E.idcat = 12
ORDER BY ARTICLE

--------

id 	ARTICLE 	TELEPHONE 	MARQUE 	ACCESSOIRE
1000 	Bluetooth Mono sans fil HTC 	Iolite T4242 	HTC 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 	6280 	BLACKBERRY 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 	E60 	NOKIA 	Bluetooth
1000 	Bluetooth Mono sans fil HTC 	L600 	SAMSUNG 	Bluetooth

**bibos** · 08/02/2012, 09h25

Envoyé par bibos

J'ai essayé d'ajouter la ligne de punkoff, ce qui me donne le bon nombre de lignes, sans champs NULL... mais avec toujours le même type d'accessoire remonté !

SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, E.denom as ACCESSOIRE
FROM afs_article A
LEFT JOIN afs_catart C ON A.id = C.idart
INNER JOIN afs_categorie B ON B.id = C.idcat
AND B.idcat =11
LEFT JOIN afs_categorie D ON B.idcat2 = D.id
LEFT OUTER JOIN afs_categorie E ON E.id = E.idcat AND E.idcat = 12
ORDER BY ARTICLE

Forcément je me suis planté en mettant E.id=E.idcat au lieu de E.id=C.idcat...

Bref, si je corrige, j'ai des champs null dans la colonne accessoire.

**Rei Ichido** · 08/02/2012, 15h28

Envoyé par bibos

A l'heure actuelle j'utilise donc deux requêtes sans doute archaïques, mais qui ont le mérite de fonctionner... mais qui me prennent un max de ressources et de temps d'exécution.

Un max à quel point ? Avez-vous des index sur les tables ? Les requêtes me semblent correctes, les volumétries sont faibles, ça devrait tourner tout seul si c'est bien indexé.

EDIT :
Votre requête ne retourne rien parce que de la table C, vous tentez une jointure simultanée avec deux fois la même table sur la même colonne. Aucune ligne ne peut a priori avoir un idcat qui corresponde à simultanément un id de categorie ayant un idcat de 11 et un autre ayant un idcat de 12. Je me permet d'ailleurs de signaler que le nommage des colonnes prête hautement à confusion !
Quelque chose du genre (avant de regarder à faire de l'optimisation) vous renvoie-t-il des résultats corrects :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, E.denom AS ACCESSOIRE
FROM afs_article A
JOIN afs_catart C ON A.id = C.idart
JOIN afs_categorie B ON B.id = C.idcat AND B.idcat=11
JOIN afs_categorie D ON B.idcat2 = D.id
-- là il faut refaire une jointure avec catart
JOIN afs_catart C2 ON A.id = C2.idart
JOIN afs_categorie E ON E.id = C2.idcat AND E.idcat=12
ORDER BY ARTICLE

**bibos** · 08/02/2012, 15h32

Envoyé par Rei Ichido

Un max à quel point ? Avez-vous des index sur les tables ? Les requêtes me semblent correctes, les volumétries sont faibles, ça devrait tourner tout seul si c'est bien indexé.

Ca met environ 140 secondes à s'exécuter quand même...
Et ça utilise plus de 800Mo de mémoire...

**Rei Ichido** · 08/02/2012, 15h47

J'ai édité mon post plus haut pour suggérer une requête.

Sur la question des perfs, il ya des index ?

**bibos** · 08/02/2012, 15h53

Envoyé par Rei Ichido

Votre requête ne retourne rien parce que de la table C, vous tentez une jointure simultanée avec deux fois la même table sur la même colonne. Aucune ligne ne peut a priori avoir un idcat qui corresponde à simultanément un id de categorie ayant un idcat de 11 et un autre ayant un idcat de 12. Je me permet d'ailleurs de signaler que le nommage des colonnes prête hautement à confusion !
Quelque chose du genre (avant de regarder à faire de l'optimisation) vous renvoie-t-il des résultats corrects :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT A.id, A.denom AS ARTICLE, B.denom AS TELEPHONE, D.denom AS MARQUE, E.denom AS ACCESSOIRE
FROM afs_article A
JOIN afs_catart C ON A.id = C.idart
JOIN afs_categorie B ON B.id = C.idcat AND B.idcat=11
JOIN afs_categorie D ON B.idcat2 = D.id
-- là il faut refaire une jointure avec catart
JOIN afs_catart C2 ON A.id = C2.idart
JOIN afs_categorie E ON E.id = C2.idcat AND E.idcat=12
ORDER BY ARTICLE

J'ai tout compris, et ça marche impeccable... Il fallait refaire une jointure sur catart!

Et donc on peut encore optimiser cette requête ?
Parce que là elle est traitée en 0.0001 sec !