Discussion :

[sialac]

Bonjour,
Je cale depuis un moment sur un problème de jointure que je ne m'explique pas...
Je dispose d'une requête assez "gourmande" (nombreuses tables, nombreux enregistrements). Cette requête, testée dans Mysql, me renvoie (par exemple) 300 enregistrements dans un temps de traitement de 0.8900 seconde (temps tout à fait raisonnable). Je veux ajouter une jointure externe dans cette requête et le temps de traitement devient extrêmement long ! 24.8114 secondes pour une même réponse.

Principe de la requête : Afficher tous les albums de tous les dessinateurs invités sur une manifestation en précisant leur rôle et en affichant les chroniques associées aux albums.

Requête de départ (qui fonctionne très bien) :

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
SELECT auteur.num_auteur, auteur.nom_auteur, auteur.prenom_auteur, auteur.pseudonyme, auteur_invite_par.num_manifestation, auteur_invite_par.presence,
manifestation.num_manifestation,
serie.num_de_la_serie, serie.nom_serie, serie.num_categorie, serie.aff_nom, serie.editeur_id,
album.num_album, album.code_chronique_bdselection, album.code_chronique_sceneario, album.epuise, album.num_dans_la_serie, album.num_de_la_serie, date_sortie,
collabore_album.num_album, collabore_album.role,
editeur.editeur_id, editeur.nom_editeur,
nom_categorie
 
FROM auteur, auteur_invite_par, manifestation, album,
        collabore_album, editeur, categorie, serie
 
WHERE auteur.num_auteur = auteur_invite_par.num_auteur
    AND auteur_invite_par.num_manifestation = manifestation.num_manifestation
    AND manifestation.num_manifestation = '$num_manif'
    AND serie.num_de_la_serie = album.num_de_la_serie
    AND collabore_album.num_album = album.num_album
    AND collabore_album.num_auteur = auteur.num_auteur
    AND serie.editeur_id = editeur.editeur_id
    AND serie.num_categorie = categorie.num_categorie
 
ORDER BY nom_auteur, prenom_auteur, serie.num_categorie, nom_serie asc,
              nom_editeur asc, num_dans_la_serie asc, role

Requête modifié pour ajouter une jointure Externe :

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
SELECT auteur.num_auteur, auteur.nom_auteur, auteur.prenom_auteur, auteur.pseudonyme, auteur_invite_par.num_manifestation, auteur_invite_par.presence,
manifestation.num_manifestation,
serie.num_de_la_serie, serie.nom_serie, serie.num_categorie, serie.aff_nom, serie.editeur_id,
rubrique_code_bedetheque,
album.num_album, album.code_chronique_bdselection, album.code_chronique_sceneario, album.epuise, album.num_dans_la_serie, album.num_de_la_serie, date_sortie,
collabore_album.num_album, collabore_album.role,
editeur.editeur_id, editeur.nom_editeur,
nom_categorie

FROM auteur, auteur_invite_par, manifestation, album,
        collabore_album, editeur, categorie, serie left outer join rubrique

ON serie.num_de_la_serie = rubrique.num_de_la_serie

WHERE auteur.num_auteur = auteur_invite_par.num_auteur
    AND auteur_invite_par.num_manifestation = manifestation.num_manifestation
    AND manifestation.num_manifestation = '$num_manif'
    AND serie.num_de_la_serie = album.num_de_la_serie
    AND collabore_album.num_album = album.num_album
    AND collabore_album.num_auteur = auteur.num_auteur
    AND serie.editeur_id = editeur.editeur_id
    AND serie.num_categorie = categorie.num_categorie

ORDER BY nom_auteur, prenom_auteur, serie.num_categorie, nom_serie asc,
              nom_editeur asc, num_dans_la_serie asc, role

Pour faciliter la lecture, j'ai mis en rouge les modifications apportées à la requête. Je précise que le rôle de cette jointure externe est de pouvoir récupérer un code relatif à la série dans la table Rubrique sachant que certaine séries ne disposent pas de ce code donc n'ont pas d'occurrences dans la table rubrique

J'avoue ne pas comprendre pourquoi le temps de traitement augmente à ce point... Est-ce que cela est du au nombre important d'enregistrement ? Et dans ce cas, comment faire pour optimiser cette requête afin de garder des temps relativement raisonnables.

Toute aide sera la bienvenue car je tourne en rond... lol

[ced]

Bonjour,

Commence par écrire toutes les jointures sur la même syntaxe que la jointure externe (INNER JOIN ... ON ...) et regarde ce que ça donne.
En effet, depuis MySQL 5, la priorité entre l'opérateur (,) et les opérateurs JOIN a changé.

De toute façon, c'est une très bonne habitude que d'écrire les jointures de manière normalisée (INNER JOIN...).

ced

[sialac]

Bonjour,

Commence par écrire toutes les jointures sur la même syntaxe que la jointure externe (INNER JOIN ... ON ...) et regarde ce que ça donne.
En effet, depuis MySQL 5, la priorité entre l'opérateur (,) et les opérateurs JOIN a changé.

De toute façon, c'est une très bonne habitude que d'écrire les jointures de manière normalisée (INNER JOIN...).

ced

Merci pour ta réponse rapide. Cependant, j'ai un soucis pour transformer la requête avec des jointures de type INNER JOIN. En effet, j'ai une table A qui doit avoir une jointure avec une table B qui elle-même doit avoir une jointure avec une table C, etc... et donc lorsque que j'ai essayé avec le code qui suit... Cela ne fonctionne pas puisque les tables sont présentes plusieurs fois à la suite du FROM.
N'ayant pas l'habitude d'écrire mes requêtes avec Inner join... peux-tu m'aider ?

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
SELECT auteur.num_auteur, auteur.nom_auteur, auteur.prenom_auteur, auteur.pseudonyme, auteur_invite_par.num_manifestation, auteur_invite_par.presence, manifestation.num_manifestation,
serie.num_de_la_serie, serie.nom_serie, serie.num_categorie, serie.aff_nom, serie.editeur_id, album.num_album, album.code_chronique_bdselection,
album.code_chronique_sceneario, album.epuise, 	album.num_dans_la_serie, album.num_de_la_serie, date_sortie,
collabore_album.num_album, collabore_album.role,
editeur.editeur_id, editeur.nom_editeur,
nom_categorie
 
FROM auteur  inner join auteur_invite_par ON auteur.num_auteur = auteur_invite_par.num_auteur, 
auteur_invite_par inner join manifestation ON auteur_invite_par.num_manifestation = manifestation.num_manifestation,
serie             inner join album ON serie.num_de_la_serie = album.num_de_la_serie,
serie             inner join editeur ON serie.editeur_id = editeur.editeur_id,
serie             inner join categorie ON serie.num_categorie = categorie.num_categorie,
album             inner join collabore_album ON collabore_album.num_album = album.num_album,
collabore_album   inner join auteur ON collabore_album.num_auteur = auteur.num_auteur,
manifestation
 
WHERE manifestation.num_manifestation = '$num_manif'
 
ORDER BY nom_auteur, prenom_auteur, serie.num_categorie, nom_serie asc, nom_editeur asc, num_dans_la_serie asc, role

[ced]

Vu le nombre de tables en jeu, il serait plus facile, pour pouvoir t'aider efficacement, qu'on puisse voir le schéma de la base (ou au moins le schéma des tables en question).
C'est possible de nous montrer ça ?

ced

[sialac]

Vu le nombre de tables en jeu, il serait plus facile, pour pouvoir t'aider efficacement, qu'on puisse voir le schéma de la base (ou au moins le schéma des tables en question).
C'est possible de nous montrer ça ?

ced

Je précise que les entités "dessine", "Mettre en couleur" et "scenarise" ont été transformé en une table appelée "Collabore_album" avec un champ "rôle" pour affecter le bon rôle (dessinateur, coloriste ou bien scénariste)

S'il est nécessaire que je joigne aussi la structure de mes tables, n'hésite pas à me le dire...

[CinePhil]

Voici ta première requête réécrite avec la syntaxe normalisée depuis 1992 pour les jointures :

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
SELECT a.num_auteur, a.nom_auteur, a.prenom_auteur, a.pseudonyme, 
  aip.num_manifestation, aip.presence,
  m.num_manifestation,
  s.num_de_la_serie, s.nom_serie, s.num_categorie, s.aff_nom, s.editeur_id,
  alb.num_album, alb.code_chronique_bdselection, alb.code_chronique_sceneario, alb.epuise, alb.num_dans_la_serie, alb.num_de_la_serie, alb.date_sortie,
  ca.num_album, ca.role,
  e.editeur_id, e.nom_editeur,
  c.nom_categorie
 
FROM auteur AS a 
INNER JOIN collabore_album AS ca ON ca.num_auteur = a.num_auteur
  INNER JOIN album AS alb ON ca.num_album = alb.num_album
    INNER JOIN serie AS s ON s.num_de_la_serie = alb.num_de_la_serie
      INNER JOIN editeur AS e ON s.editeur_id = e.editeur_id
      INNER JOIN categorie AS c ON s.num_categorie = c.num_categorie
INNER JOIN auteur_invite_par AS aip ON a.num_auteur = aip.num_auteur
  INNER JOIN manifestation AS m ON aip.num_manifestation = m.num_manifestation
 
WHERE m.num_manifestation = '$num_manif'
 
ORDER BY a.nom_auteur, a.prenom_auteur, 
  s.serie.num_categorie, s.nom_serie,
  e.nom_editeur, e.num_dans_la_serie, e.role

J'y ai de plus mis des alias et je l'ai indentée.
Tu remarqueras aussi que j'ai ajouté les alias sur les colonnes du SELECT qui n'étaient pas préfixées du nom de la table. J'espère que mes suppositions sont justes.
J'ai par ailleurs séparé le SELECT par tables d'origine des colonnes.
J'ai aussi supprimé du ORDER BY les ASC qui sont inutiles puisque c'est la valeur par défaut.
Tout ceci rend la requête plus lisible.
Je n'ai pas vérifié avec le schéma mais ça doit être ça, pour autant que ta première requête soit bonne.

On ajoute LEFT OUTER JOIN :

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
26
SELECT a.num_auteur, a.nom_auteur, a.prenom_auteur, a.pseudonyme, 
  aip.num_manifestation, aip.presence,
  m.num_manifestation,
  s.num_de_la_serie, s.nom_serie, s.num_categorie, s.aff_nom, s.editeur_id,
  r.rubrique_code_bedetheque
  alb.num_album, alb.code_chronique_bdselection, alb.code_chronique_sceneario, alb.epuise, alb.num_dans_la_serie, alb.num_de_la_serie, alb.date_sortie,
  ca.num_album, ca.role,
  e.editeur_id, e.nom_editeur,
  c.nom_categorie
 
FROM auteur AS a 
INNER JOIN collabore_album AS ca ON ca.num_auteur = a.num_auteur
  INNER JOIN album AS alb ON ca.num_album = alb.num_album
    INNER JOIN serie AS s ON s.num_de_la_serie = alb.num_de_la_serie
      INNER JOIN editeur AS e ON s.editeur_id = e.editeur_id
      INNER JOIN categorie AS c ON s.num_categorie = c.num_categorie
      LEFT OUTER JOIN rubrique AS r ON s.num_de_la_serie = r.num_de_la_serie
 
INNER JOIN auteur_invite_par AS aip ON a.num_auteur = aip.num_auteur
  INNER JOIN manifestation AS m ON aip.num_manifestation = m.num_manifestation
 
WHERE m.num_manifestation = '$num_manif'
 
ORDER BY a.nom_auteur, a.prenom_auteur, 
  s.serie.num_categorie, s.nom_serie,
  e.nom_editeur, e.num_dans_la_serie, e.role

Une première remarque quant aux performances :
Si la colonne 'num_manifestation' est de type numérique, inutile d'entourer sa valeur par des apostrophes. Ça peut inciter MySQL à faire une opération de conversion du type totalement inutile.

D'ailleurs, j'espère que toutes les colonens figurant dans les conditions de jointure sont des clés étrangères de type entier faisant référence à des clés primaires elles-mêmes de type entier !

J'espère aussi que toutes ces clés étrangères sont indexées !

Pour pouvoir comprendre pourquoi la requête devient lente, il faudrait examiner le plan d'exécution de la requête. Pour cela, fait un EXPLAIN de la requête et poste ici le résultat. Ça permettra de voir quels index sont utilisés.

[sialac]

Voici ta première requête réécrite avec la syntaxe normalisée depuis 1992 pour les jointures ...

Et bien... Je n'ai qu'une chose à dire... Un GRAND Merci ! J'obtiens même, après ajout de la jointure externe, une vitesse d'exécution meilleure que ma requête initiale...

Je vais donc de ce pas étudier bien en détail cette syntaxe normalisée afin d'optimiser les nombreuses autres requêtes présentes sur mon site.

[sialac]

Finalement, je ne referme pas tout de suite le sujet puisque j'ai un autre problème à soulever avec cette même requête...

Lorsqu'un auteur N'A PAS d'occurrence d'album dans la table COLLABORE_ALBUM (même si cela est peu fréquent... Ça peut arriver si les ouvrages de cet auteur ne sont pas connus par exemple), il N'EST PAS repris dans le résultat de la requête... (Je précise que ce pb était déjà présent dans ma toute première requête initiale).

J'ai tenté de corriger la requête avec un LEFT OUTER JOIN supplémentaire mais cela n'a pas fonctionné du fait que "ne pas avoir d'album" entraine aussi de ne pas avoir d'occurrence dans la table SERIE et dans la table ALBUM !

Je reprend donc la requête corrigée précédemment ci dessous (désolé mais je n'ai pas repris les alias) :

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
 
SELECT auteur.num_auteur, auteur.nom_auteur, auteur.prenom_auteur, auteur.pseudonyme, 
                  auteur_invite_par.num_manifestation, auteur_invite_par.presence,
                  manifestation.num_manifestation,
                  serie.num_de_la_serie, serie.nom_serie, serie.num_categorie, serie.aff_nom, serie.editeur_id,
				  rubrique.rubrique_code_bedetheque,
                  album.num_album, album.code_chronique_bdselection, album.code_chronique_sceneario, album.epuise, album.num_dans_la_serie, album.num_de_la_serie, album.date_sortie,
                  collabore_album.num_album, collabore_album.role,
                  editeur.editeur_id, editeur.nom_editeur,
                  categorie.nom_categorie
                FROM auteur 
                 INNER JOIN collabore_album ON collabore_album.num_auteur = auteur.num_auteur
                   INNER JOIN album ON collabore_album.num_album = album.num_album
                     INNER JOIN serie ON serie.num_de_la_serie = album.num_de_la_serie
                       INNER JOIN editeur ON serie.editeur_id = editeur.editeur_id
                       INNER JOIN categorie ON serie.num_categorie = categorie.num_categorie
				       LEFT OUTER JOIN rubrique ON serie.num_de_la_serie = rubrique.num_de_la_serie
                 INNER JOIN auteur_invite_par ON auteur.num_auteur = auteur_invite_par.num_auteur
                   INNER JOIN manifestation ON auteur_invite_par.num_manifestation = manifestation.num_manifestation
               WHERE manifestation.num_manifestation = $num_manif
               ORDER BY auteur.nom_auteur, auteur.prenom_auteur, 
                        serie.num_categorie, serie.nom_serie,
                        editeur.nom_editeur, album.num_dans_la_serie, collabore_album.role

Je compte sur l'efficacité des lecteurs de ce post pour m'aider à résoudre ce deuxième problème...

[CinePhil]

Si tu fais ceci :

Code :

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

1
2
FROM auteur 
LEFT OUTER JOIN collabore_album ON collabore_album.num_auteur = auteur.num_auteur

Tu auras tous les auteurs, même s'il n'ont pas de correspondance dans collabore_album.

Par contre, comme les jointures s'enchaînent de la façon suivante :
auteur => collabore_album => album => serie => editeur + categorie + rubrique

Tu n'auras pas ce qui suit collabore_album pour tous ces auteurs.

Ton besoin de départ :

Afficher tous les albums de tous les dessinateurs invités sur une manifestation en précisant leur rôle et en affichant les chroniques associées aux albums.

Je suppose que 'dessinateur' dans cette exigence = 'auteur' dans la requête ?

Si tu souhaites afficher tous les auteurs invités à ta manifestation, la requête est bonne.
Si tu souhaites afficher tous les albums présentés à la manifestation, il faut peut-être changer l'ordre des jointures.

À toi de préciser ton besoin.

PS : Pourquoi n'as-tu pas gardé les alias ?

[sialac]

Si tu souhaites afficher tous les auteurs invités à ta manifestation, la requête est bonne.
Si tu souhaites afficher tous les albums présentés à la manifestation, il faut peut-être changer l'ordre des jointures.

À toi de préciser ton besoin.

Ma requête initiale reste globalement la même puisque l'intérêt est d'afficher tous les auteurs invités à une manifestation. Mais le problème est que les auteurs n'ayant pas d'albums n'apparaissent pas...
Si je dis que je veux tous les albums des auteurs invités, cela revient au même car les auteurs n'ayant pas d'album n'apparaitront toujours pas...

PS : Pourquoi n'as-tu pas gardé les alias ?

Pour des raisons de lisibilité, je ne trouve pas d'avantages particuliers à utiliser des alias (sauf peut être pour un gain de place) à moins bien sur que cela aurait une incidence sur la vitesse de la requête (mais j'en doute)

[CinePhil]

Quand je disais "la requête est bonne", c'est bien sûr en ajoutant le LEFT OUTER JOIN tel que je l'ai donné !

[sialac]

Quand je disais "la requête est bonne", c'est bien sûr en ajoutant le LEFT OUTER JOIN tel que je l'ai donné !

Je l'avais compris ainsi... En fait, ce que je veux c'est "Afficher tous les auteurs invités à une manifestation Y COMPRIS ceux qui n'ont pas d'album"... C'est plus clair ainsi.

[CinePhil]

Je l'avais compris ainsi... En fait, ce que je veux c'est "Afficher tous les auteurs invités à une manifestation Y COMPRIS ceux qui n'ont pas d'album"... C'est plus clair ainsi.

Oui, c'est clair et c'est comme ça que je l'avais compris !

Avec le LEFT OUTER JOIN, ça ne donne pas le bon résultat ?

[sialac]

Avec le LEFT OUTER JOIN, ça ne donne pas le bon résultat ?

Non, malheureusement, la requête ne me renvoie que les auteurs invités ayant au moins un album et ignore ceux qui n'en ont pas.

Voilà le code tel que je l'ai modifié...

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
SELECT auteur.num_auteur, auteur.nom_auteur, auteur.prenom_auteur, auteur.pseudonyme, 
                  auteur_invite_par.num_manifestation, auteur_invite_par.presence,
                  manifestation.num_manifestation,
                  serie.num_de_la_serie, serie.nom_serie, serie.num_categorie, serie.aff_nom, serie.editeur_id,
				  rubrique.rubrique_code_bedetheque,
                  album.num_album, album.code_chronique_bdselection, album.code_chronique_sceneario, album.epuise, album.num_dans_la_serie, album.num_de_la_serie, album.date_sortie,
                  collabore_album.num_album, collabore_album.role,
                  editeur.editeur_id, editeur.nom_editeur,
                  categorie.nom_categorie
                FROM auteur 
                 LEFT OUTER JOIN collabore_album ON collabore_album.num_auteur = auteur.num_auteur
                   INNER JOIN album ON collabore_album.num_album = album.num_album
                     INNER JOIN serie ON serie.num_de_la_serie = album.num_de_la_serie
                       INNER JOIN editeur ON serie.editeur_id = editeur.editeur_id
                       INNER JOIN categorie ON serie.num_categorie = categorie.num_categorie
				       LEFT OUTER JOIN rubrique ON serie.num_de_la_serie = rubrique.num_de_la_serie
                 INNER JOIN auteur_invite_par ON auteur.num_auteur = auteur_invite_par.num_auteur
                   INNER JOIN manifestation ON auteur_invite_par.num_manifestation = manifestation.num_manifestation
               WHERE manifestation.num_manifestation = $num_manif
               ORDER BY auteur.nom_auteur, auteur.prenom_auteur, 
                        serie.num_categorie, serie.nom_serie,
                        editeur.nom_editeur, album.num_dans_la_serie, collabore_album.role

[CinePhil]

Je pense que c'est parce que MySQL fait d'abord les jointures internes avant les jointures externes.
Essaie en propageant la jointure externe sur les internes. En d'autres mots, mets LEFT OUTER JOIN à la place de tous les INNER JOIN qui sont entre les deux LEFT OUTER JOIN déjà existants.

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
SELECT auteur.num_auteur, auteur.nom_auteur, auteur.prenom_auteur, auteur.pseudonyme, 
  auteur_invite_par.num_manifestation, auteur_invite_par.presence,
  manifestation.num_manifestation,
  serie.num_de_la_serie, serie.nom_serie, serie.num_categorie, serie.aff_nom, serie.editeur_id,
  rubrique.rubrique_code_bedetheque,
  album.num_album, album.code_chronique_bdselection, album.code_chronique_sceneario, album.epuise, album.num_dans_la_serie, album.num_de_la_serie, album.date_sortie,
  collabore_album.num_album, collabore_album.role,
  editeur.editeur_id, editeur.nom_editeur,
  categorie.nom_categorie
FROM auteur 
LEFT OUTER JOIN collabore_album ON collabore_album.num_auteur = auteur.num_auteur
  LEFT OUTER JOIN album ON collabore_album.num_album = album.num_album
    LEFT OUTER JOIN serie ON serie.num_de_la_serie = album.num_de_la_serie
      LEFT OUTER JOIN editeur ON serie.editeur_id = editeur.editeur_id
      LEFT OUTER JOIN categorie ON serie.num_categorie = categorie.num_categorie
      LEFT OUTER JOIN rubrique ON serie.num_de_la_serie = rubrique.num_de_la_serie
INNER JOIN auteur_invite_par ON auteur.num_auteur = auteur_invite_par.num_auteur
  INNER JOIN manifestation ON auteur_invite_par.num_manifestation = manifestation.num_manifestation
WHERE manifestation.num_manifestation = $num_manif
ORDER BY auteur.nom_auteur, auteur.prenom_auteur, 
  serie.num_categorie, serie.nom_serie,
  editeur.nom_editeur, album.num_dans_la_serie, collabore_album.role

[sialac]

Je pense que c'est parce que MySQL fait d'abord les jointures internes avant les jointures externes.
Essaie en propageant la jointure externe sur les internes. En d'autres mots, mets LEFT OUTER JOIN à la place de tous les INNER JOIN qui sont entre les deux LEFT OUTER JOIN déjà existants.

SUPER ! C'est la bonne solution... Je n'avais fait des test qu'en remplaçant les 3 premiers INNER JOIN mais l'idée ne m'était pas venue qu'il fallait tous les grouper.
Je renouvelle donc le MERCI que j'avais dis précédemment et l'on va pouvoir clôturer ce sujet avec la mention RESOLU