Enigme sur deux requêtes apparemment semblables

Version imprimable

Bonjour,
J'ai un problème qui me laisse perplexe. Je ne peux même pas le transposer avec des tables simples donc pour la première foi, je vais copier ici mes requêtes telles que je les utilise, en espérant que ce ne sera pas trop infernal à lire :
La requête suivante s'effectue en 0,047s, donc pas de problème :

Code:

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
27
SELECT book.*,
             simulation.updatedate as simulationdate,
             collection.name AS collectionName,
             groups.name AS groupname,
             house.name AS housename,
             CASE WHEN (contributor.firstname = NULL OR contributor.firstname ='') THEN contributor.name ELSE CONCAT(contributor.name,', ', contributor.firstname) END AS contributor1Name,
             CONCAT(editor.name,', ',editor.firstname) AS editorname,
             article.articleid,
             article.udcode,
             article.isbn13,
             article.gencod,
             article.senddate,
             CASE WHEN (priceandtirage.isvalidated = '1') THEN priceandtirage.realprice END AS finalprice
      FROM book LEFT OUTER JOIN (article LEFT OUTER JOIN priceandtirage ON (article.articleid = priceandtirage.articleid)) ON (book.bookid = article.bookid)
                LEFT OUTER JOIN simulation ON (book.simulationid = simulation.simulationid),
                contributor,editor,collection,groups,house,editionplan
      WHERE editionplan.bookid = book.bookid 
      AND book.collectionid = collection.collectionid
      AND book.groupid = groups.groupid
      AND book.houseid = house.houseid
      AND book.contributor1id = contributor.contributorid
      AND book.editorid = editor.editorid
      AND book.assigned = '1'
      AND editionplan.year = 2008 
      AND editionplan.month = 2
      GROUP BY book.bookid
      ORDER BY housename ASC, groupname ASC, title ASC

Maintenant, la requête suivante (la même que la précédente à l'exception du rajout d'une condition qui me semble sans grande influence a priori) mouline un temps infini, et finit par planter MySql (et mon PC !) :

Code:

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
27
28
SELECT book.*,
             simulation.updatedate as simulationdate,
             collection.name AS collectionName,
             groups.name AS groupname,
             house.name AS housename,
             CASE WHEN (contributor.firstname = NULL OR contributor.firstname ='') THEN contributor.name ELSE CONCAT(contributor.name,', ', contributor.firstname) END AS contributor1Name,
             CONCAT(editor.name,', ',editor.firstname) AS editorname,
             article.articleid,
             article.udcode,
             article.isbn13,
             article.gencod,
             article.senddate,
             CASE WHEN (priceandtirage.isvalidated = '1') THEN priceandtirage.realprice END AS finalprice
      FROM book LEFT OUTER JOIN (article LEFT OUTER JOIN priceandtirage ON (article.articleid = priceandtirage.articleid)) ON (book.bookid = article.bookid)
                LEFT OUTER JOIN simulation ON (book.simulationid = simulation.simulationid),
                contributor,editor,collection,groups,house,editionplan
      WHERE editionplan.bookid = book.bookid 
      AND book.collectionid = collection.collectionid
      AND book.groupid = groups.groupid
      AND book.houseid = house.houseid
      AND book.contributor1id = contributor.contributorid
      AND book.editorid = editor.editorid
      AND book.assigned = '1'
      AND editionplan.year = 2008 
      AND editionplan.month = 2
      AND book.groupid = 12
      GROUP BY book.bookid
      ORDER BY housename ASC, groupname ASC, title ASC

A moins que je n'aie pas les yeaux en face des trous, la seule chose qui a changé par rapport à la première requête, c'est la condition suivante sui a été rajoutée :

Code:

AND book.groupid = 12

Or, par rapport au résultat de la première requête (101 lignes), 23 correspondent à cette condition supplémentaire.

J'ai essayé, à partir de la seconde requête, d'enlever progressivement des éléments pour voir où ça coince mais je n'ai pas réussi à trouver. Je pencherais pour le LEFT OUTER JOIN imbriqué mais c'est sans aucune certitude.

Pour info, le table "priceandtirage" contient un index sur la colonne "articleid" et les tables "simulation" et "article" contiennent un index sur la colonne "bookid".

Qu'est ce qui peut bien expliquer une telle différence de comportement ??
Je suis vraiment perplexe.
Merci pour votre aide.

06/02/2008, 00h37
Antoun

Tu devrais commencer par écrire toutes tes jointures dans le FROM, au lieu de mélanger des jointures internes dans le WHERE avec des jointures externes normalisées. Je ne garantis pas que l'optimiseur comprendra mieux, mais nous si !

Bonjour et merci pour cette réponse.
Voici les 2 requêtes améliorées selon tes recommandations :

Code:

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 book.*,
       simulation.updatedate as simulationdate,
       collection.name AS collectionName,
       groups.name AS groupname,
       house.name AS housename,
       CASE WHEN (contributor.firstname = NULL OR contributor.firstname ='') THEN contributor.name ELSE CONCAT(contributor.name,', ', contributor.firstname) END AS contributor1Name,
       CONCAT(editor.name,', ',editor.firstname) AS editorname,
       article.articleid,
       article.udcode,
       article.isbn13,
       article.gencod,
       article.senddate,
       CASE WHEN (priceandtirage.isvalidated = '1') THEN priceandtirage.realprice END AS finalprice
FROM book JOIN editionplan ON (book.bookid = editionplan.bookid)
	  JOIN collection ON (book.collectionid = collection.collectionid)
     	  JOIN groups ON (book.groupid = groups.groupid)
	  JOIN house ON (book.houseid = house.houseid)
	  JOIN contributor ON (book.contributor1id = contributor.contributorid)
	  JOIN editor ON (book.editorid = editor.editorid)
	  LEFT OUTER JOIN (article LEFT OUTER JOIN priceandtirage ON (article.articleid = priceandtirage.articleid)) ON (book.bookid = article.bookid)
          LEFT OUTER JOIN simulation ON (book.simulationid = simulation.simulationid)
WHERE book.assigned = '1'
AND editionplan.year = 2008
AND editionplan.month = 2
GROUP BY book.bookid
ORDER BY housename ASC, groupname ASC, title ASC

Code:

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
27
SELECT book.*,
       simulation.updatedate as simulationdate,
       collection.name AS collectionName,
       groups.name AS groupname,
       house.name AS housename,
       CASE WHEN (contributor.firstname = NULL OR contributor.firstname ='') THEN contributor.name ELSE CONCAT(contributor.name,', ', contributor.firstname) END AS contributor1Name,
       CONCAT(editor.name,', ',editor.firstname) AS editorname,
       article.articleid,
       article.udcode,
       article.isbn13,
       article.gencod,
       article.senddate,
       CASE WHEN (priceandtirage.isvalidated = '1') THEN priceandtirage.realprice END AS finalprice
FROM book JOIN editionplan ON (book.bookid = editionplan.bookid)
	  JOIN collection ON (book.collectionid = collection.collectionid)
     	  JOIN groups ON (book.groupid = groups.groupid)
	  JOIN house ON (book.houseid = house.houseid)
	  JOIN contributor ON (book.contributor1id = contributor.contributorid)
	  JOIN editor ON (book.editorid = editor.editorid)
	  LEFT OUTER JOIN (article LEFT OUTER JOIN priceandtirage ON (article.articleid = priceandtirage.articleid)) ON (book.bookid = article.bookid)
          LEFT OUTER JOIN simulation ON (book.simulationid = simulation.simulationid)
WHERE book.assigned = '1'
AND editionplan.year = 2008
AND editionplan.month = 2
AND book.groupid = 12
GROUP BY book.bookid
ORDER BY housename ASC, groupname ASC, title ASC

J'espère que c'est plus clair et que vous pourrez m'aider.

J'ai moi-même ré-essayé ces "nouvelles" requêtes mais le résultat est le même : quasi instantané pour la 1ère et infiniment long pour la seconde !

Encore merci pour votre aide !

Effectivement c'est plus clair :king:

Pour en finir avec les réécritures je conseillerais :

Code:

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
27
28
 
SELECT book.*,
       simulation.updatedate AS simulationdate,
       collection.name AS collectionName,
       groups.name AS groupname,
       house.name AS housename,
       CASE WHEN (contributor.firstname IS NULL OR contributor.firstname ='') THEN contributor.name ELSE CONCAT(contributor.name,', ', contributor.firstname) END AS contributor1Name,
       CONCAT(editor.name,', ',editor.firstname) AS editorname,
       article.articleid,
       article.udcode,
       article.isbn13,
       article.gencod,
       article.senddate,
       CASE WHEN (priceandtirage.isvalidated = '1') THEN priceandtirage.realprice END AS finalprice
FROM book JOIN editionplan ON (book.bookid = editionplan.bookid)
	  JOIN collection ON (book.collectionid = collection.collectionid)
     	  JOIN groups ON (book.groupid = groups.groupid)
	  JOIN house ON (book.houseid = house.houseid)
	  JOIN contributor ON (book.contributor1id = contributor.contributorid)
	  JOIN editor ON (book.editorid = editor.editorid)
	  LEFT OUTER JOIN article ON (book.bookid = article.bookid)
          LEFT OUTER JOIN priceandtirage ON (article.articleid = priceandtirage.articleid) 
          LEFT OUTER JOIN simulation ON (book.simulationid = simulation.simulationid)
WHERE book.assigned = '1'
AND editionplan.year = 2008
AND editionplan.month = 2
GROUP BY book.bookid
ORDER BY housename ASC, groupname ASC, title ASC

A noter que "= NULL" donne toujours NULL, donc le test souhaité est vraissemblablement "IS NULL".

Pour être fixé rien ne vaut un EXPLAIN sur les deux requêtes car ni vue ni connue la condition en plus doit changer le plan d'exécution. Il se pourrait par exemple que l'utilisation d'un index sur book.groupid chasse un autre index pourtant bien plus utile (un seul index par table peut être utilisé dans une même requête) mais ça semble ne pas être le cas.

Au nez je sens bien l'inversion entre de l'ordre entre book et editionplan. Un index sur editionplan.bookid serait peut être la clef...

06/02/2008, 16h25
jgfa9

2 pièce(s) jointe(s)

Bonjour,
La correction de la requête (suppression des LEFT OUTER JOIN "imbriqués") corrige le problème !
Je pensais que les tables de part et d'autre de la jointure devaient avoir une colonne commune, je ne savais pas qu'on pouvait faire comme ça...
Par ailleurs, pour bien comprendre, et si ce n'est pas trop demander, pourriez-vous m'expliquer comment analyser les résultats des EXPLAIN (pièces jointes) effectués sur la requête initiale puis sur la requête corrigée (à quoi en particulier faut-il faire attention ?)
Vaut-il quand même mieux que je rajoute un index sur editionplan$bookid et laisser l'optimiseur choisir le meilleur ?
Merci par avance pour vos explications... et surtout pour votre précieuse aide !
13/02/2008, 00h45
Sivrît
Pour un supplément d'info sur EXPLAIN les articles ne manquent pas (entre le site officiel, google et peut-être même ici ;)).

Pour un déchiffrage de ces cas ci...
Je commence par la version modifiée car elle correspond plus au cas normal.
- MySQL commence par la table "groups" car il a noté qu'on y accède par une valeur fixe("const") de clef primaire donc il tire le groupe concerné et on n'en parle plus. La colonne extra indique "Using temporary; Using filesort". Ca veut dire qu'il y aura création d'une table temporaire et tri. Même si le tri est fait en dernier ces mentions sont toujours données sur la première ligne. Idéalement on tente de les éviter (surtout pour des requêtes très fréquentes et critiques en termes de performances) mais ici ça ne va pas être possible.
- Ensuite on va récupérer tous les "editionplan" ("ALL") et restreindre le résultat via des prédicats du WHERE ("Using where", ce qui fait référence à "editionplan.year = 2008 AND editionplan.month = 2"). MySQL estime ici qu'il va devoir passer en revue 9356 enregistrements (c'est une estimation d'après ses statistiques internes - ici de la taille de la table - qui lui servent à décider de par quel bout s'y prendre. Elles ne sont pas nécessairement précises, loin de là). A moins de souhaiter tout récupérer, "ALL" est ce que l'on souhaite ne jamais voir. Un index (et pas deux) sur le couple (editionplan.year, editionplan.month) permettrait d'avoir directement les "editionplan" voulus sans parcourir toute la table. Mais si cette recherche est rare et que la requête est assez rapide il est possible de faire sans.
  
  A noter qu'il serait aussi possible que cet index existe mais que MySQL décide de ne pas l'utiliser. Un index est appétissant s'il est discriminant. Si ses statistiques lui disent que l'index retournera trop de résultats (par exemple le champ ne prend que 3 valeurs différentes sur 2000 enregistrements), MySQL fera sans car parcourir la table ira plus vite. Dans de rares cas les statistiques peuvent être à côté de la plaque et mener à de mauvaises décisions, auquel cas un "ANALYSE TABLE" peut dépanner.
- Ensuite pour chaque enregistrement trouvé jusqu'ici on accède aux tables "book", "collection", "contributor", "editor" et "house" via leur clefs primaires. C'est bien et rapide. "eq_ref" indique que l'on récupère au plus un enregistrement donc ces jointures ne vont pas démultiplier le nombre de résultats. La colonne "ref" indique à chaque fois d'où vient la valeur cherchée. Une restriction est appliquée sur "book" ("Using where"), ce qui pourrait faire penser indexation, mais ça ne semble pas être assez sélectif pour en valoir la peine.
- La jointure sur "article" utilise bien l'index disponible. Le "ref" indique qu'il pourrait y en avoir plusieurs à chaque fois mais MySQL estime n'en trouver qu'un. Si c'est vraiment toujours le cas l'index pourrait être déclaré UNIQUE, sinon c'est juste une indication qu'il y a peu d'articles par livre (ce qui rend l'index d'autant plus utile).
- Enfin on récupère "priceandtirage" et "simulation" à nouveau par clef primaire. Curieusement MySQL indique disposer de deux clefs dont la clef primaire pour "priceandtirage" ("PRIMARY,priceandtirage$articleid") et utiliser la clef primaire pour la comparer à "article.articleid"... pourtant ça ne semble pas coller avec la requête. Enfin, dans tous les cas c'est indexé. Se pourrait-il que la clef primaire soit "articleid" et soit indexée deux fois ?
Sur l'ancienne requête ça commence à l'identique, avec un "group" constant. Et ensuite ça déraille : On passe sur plusieurs tables en "ALL" sans même avoir de restriction dessus (pas de "using where"). Pour citer la documentation (traduction revue par mes soins car celle du site est approximative) :

Citation:

Envoyé par http://dev.mysql.com/doc/refman/5.0/en/explain.html

Une analyse complète de la table sera faîte pour chaque combinaison de lignes issue des tables précédentes. Ce n'est pas bon si c'est la première table à ne pas être une jointure de type const et c'est très mauvais dans les autres cas.

Avec les statistiques de MySQL ça veut dire que l'on va faire grosso modo 36*71*273*5464 passes sur les 8700 "book". Le "using where" indique qu'ici on va enfin éliminer les combinaisons de house/editor/collection/contributor qui ne correspondent à aucun livre. Les dernières jointures qui arrivent après sont indexées, mais elles seront faites pour tous les livres qui ont "book.assigned = '1'" donc probablement beaucoup plus que nécessaire car la restriction sur "editionplan" ne se fait qu'à la fin.
Bref MySQL se plante en beauté.

editionplan.bookid, comme toutes les clefs étrangères, fait un bon candidat à l'indexation. Basiquement un index prend de la place et un peu de temps (pas grand chose) lors des insertions/suppressions (ou des modifications sur les colonnes indexées). C'est un peu un avis personnel, mais à moins d'avoir une quantité de données astronomique ou une très forte pression sur les insertions (si les ajouts dans une table sont critiques c'est une autre affaire) je dirais qu'il vaut mieux pécher par excès... sans pour autant abuser et indexer toutes les combinaisons de colonnes. Les coûts sont relativement faibles et les gains potentiellement énormes, et ce d'autant plus que les tables grossissent.

Sinon il faut voir au cas par cas suivant ses requêtes. Tant que l'on n'a pas des centaines de milliers d'enregistrements ajouter et retirer des indexes est facile donc on peu indexer à tout va sur une machine de test, voir ce qui sert, ce qu'on gagne, et retirer le superflu.

Une dernière note : Les pièces jointes excel ont l'avantage de bien tout donner en colonnes bien formatées... seulement avec mon linux ça m'oblige à lire le xml directement et là c'est moins bien :aie:

Edit: fausse manip, posté avant d'avoir fini
13/02/2008, 14h10
jgfa9

Merci mille fois pour ces indications si précises et si enrichissantes !