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.