EXPLAIN : résultats différents selon version de MySql

Version imprimable

22/11/2010, 11h53
jgfa9

2 pièce(s) jointe(s)

EXPLAIN : résultats différents selon version de MySql

Bonjour,
J'utilise une base de données locale, pour tester (version 5.1.30) et une base sur le serveur, en production (version 5.0.41).

J'ai écrit une requête qui fonctionne très bien en local mais qui "mouline" sur le serveur et je n'arrive pas à comprendre pourquoi.
J'ai déjà vérifié et les indexs sont identiques.

En pièces jointes les résultats des EXPLAIN.

En outre, je ne comprends pas pourquoi, dans le EXPLAIN du serveur, pour les 4 premières lignes, la clé utilisée ("key") n'est pas celle indiquée dans la liste des clés possibles ("possible_keys").

Est-ce lié à la différence de version de MySql ?

Merci par avance pour votre aide.
22/11/2010, 12h51
CinePhil

Bizarre, on dirait deux explain de deux requêtes différentes !

Est-ce le cas ?

Sinon on peut voir la requête ?

Et le volume de données est-il sensiblement différent entre le local et le serveur ? Si oui, sur quelles tables ?

Voici la requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT book.bookid,
           book.title,
           collection.name AS collectionname,
           article.gencod,
           contributor.contributorid,
           contributor.name AS contributorname,
           contributor.firstname AS contributorfirstname,
           function.functionid,
           function.name AS functionname,
           CASE WHEN book.firstpublicationdate != '' THEN DATE_FORMAT(STR_TO_DATE(CONCAT('1/',book.firstpublicationdate),'%d/%m/%Y'),'%d/%m/%Y') ELSE '' END AS firstpublicationdate,
           book.monthpos,
           book.yearpos 
FROM book JOIN bookcontributor ON (book.bookid=bookcontributor.bookid) JOIN contributor ON (contributor.contributorid=bookcontributor.contributorid) JOIN function ON (function.functionid=bookcontributor.functionid) 
JOIN article ON (book.bookid=article.bookid) 
JOIN collection ON (book.collectionid=collection.collectionid) 
LEFT JOIN (contracteditionbook JOIN (contractedition JOIN contract ON (contract.contractid=contractedition.contractid)) ON (contracteditionbook.editionid=contractedition.editionid)) ON (contracteditionbook.bookid=book.bookid  AND contract.contributorid=contributor.contributorid AND contract.functionid=function.functionid) 
WHERE book.projecttype != 'PLV' 
AND book.projecttype != 'ASSORT' 
AND book.projecttype != 'PACK' 
AND book.houseid=1 
AND CASE WHEN book.firstpublicationdate != '' THEN DATEDIFF(DATE_FORMAT(STR_TO_DATE(CONCAT('1/',book.firstpublicationdate),'%d/%m/%Y'),'%Y-%m-%d'),'2010-09-30')<=0 ELSE DATEDIFF(DATE_FORMAT(STR_TO_DATE(CONCAT('1/',book.monthpos,'/',book.yearpos),'%d/%m/%Y'),'%Y-%m-%d'),'2010-09-30')<=0 END 
AND (contracteditionbook.editionid IS NULL)

J'espère qu'elle ne sera pas trop fastidieuse à lire.

Les données en local et sur le serveur sont sensiblement les mêmes.

Merci !

J'ai l'impression qu'il y a un gros mélange dans les jointures !
J'ai réussi à reconstituer l'enchaînement de jointures suivant :
Code:

1 2 3 4 5 6 7 8 9 FROM book AS b JOIN bookcontributor AS bc ON b.bookid = bc.bookid JOIN contributor AS c ON c.contributorid = bc.contributorid JOIN contract AS co ON co.contributorid = c.contributorid JOIN contractedition AS ce ON co.contractid = ce.contractid JOIN function AS f ON f.functionid = bc.functionid JOIN article AS a ON b.bookid = a.bookid JOIN collection AS col ON b.collectionid = col.collectionid LEFT JOIN contracteditionbook AS ceb ON cceb.bookid = b.bookid
Mais il reste ces conditions de jointures :
Code:

1 2 AND contract.functionid=function.functionid ON contracteditionbook.editionid = contractedition.editionid
Comme contract est déjà jointe à deux tables, si la condition de jointure est nécessaire, il te faut une nouvelle instance de cette table.
Idem pour contract edition qui est déjà jointe à contract et contracteditionbokk qui est déjà jointe à book qui est déjà jointe à plusieurs tables.

Le cheminement des jointures entre les tables doit refléter le schéma de la BDD. Soit tu as une boucle dans tes associations entre tables, soit tu as des jointures inutiles.
C'est peut-être ce mélange qui fait analyser la requête de manière différente entre le serveur et le local.

Voici ta requête récrite (sans les conditions supplémentaires mentionnées ci-dessus) :
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 29 30 31 32 33 34 35 36 37 SELECT b.bookid, b.title, col.name AS collectionname, a.gencod, c.contributorid, c.name AS contributorname, c.firstname AS contributorfirstname, f.functionid, f.name AS functionname, CASE WHEN book.firstpublicationdate <> '' THEN DATE_FORMAT(STR_TO_DATE(CONCAT('1/',book.firstpublicationdate),'%d/%m/%Y'),'%d/%m/%Y') ELSE '' END AS firstpublicationdate, book.monthpos, book.yearpos FROM book AS b JOIN bookcontributor AS bc ON b.bookid = bc.bookid JOIN contributor AS c ON c.contributorid = bc.contributorid JOIN contract AS co ON co.contributorid = c.contributorid JOIN contractedition AS ce ON co.contractid = ce.contractid JOIN function AS f ON f.functionid = bc.functionid JOIN article AS a ON b.bookid = a.bookid JOIN collection AS col ON b.collectionid = col.collectionid LEFT JOIN contracteditionbook AS ceb ON cceb.bookid = b.bookid WHERE b.projecttype <> 'PLV' AND b.projecttype <> 'ASSORT' AND b.projecttype <> 'PACK' AND b.houseid = 1 AND CASE WHEN b.firstpublicationdate <> '' THEN DATEDIFF(DATE_FORMAT(STR_TO_DATE(CONCAT('1/', b.firstpublicationdate), '%d/%m/%Y'), '%Y-%m-%d'), '2010-09-30') <= 0 ELSE DATEDIFF(DATE_FORMAT(STR_TO_DATE(CONCAT('1/',b.monthpos,'/', b.yearpos), '%d/%m/%Y'), '%Y-%m-%d'), '2010-09-30') <= 0 END AND ceb.editionid IS NULL
N'imbrique pas les JOIN, à la manière d'Access, mais fait toujours un JOIN ON condition. Il peut y a voir plusieurs conditions dans une jointure mais toujours entre les mêmes tables.

Le signe "différent de" est en SQL <> et non pas !=.

Aère et indente le code, c'est plus facile à lire.

Utilise des alias, ça réduit l'écriture de la requête et peut aussi améliorer sa lecture.

Espérant t'avoir mis sur une piste pour résoudre ton problème.

22/11/2010, 14h06
jgfa9

D'abord merci d'avoir pris le temps de me répondre ainsi !

Concernant la requête (là encore, d'abord merci pour les différents conseils), j'ai fait des jointure "imbriquées" justement parce que sinon je ne parvenais pas au bon résultat (et ainsi, cela fonctionne, tout du moins en local).

Pour clarifier le problème, les tables réellement concernées par le problème sont effectivement (et par ordre chronologique d'enregistrement dans la base) :

book : fiche livre (bookid)

bookcontributor : n contributeurs d'un livre avec fonction (ex. auteur, illustrateur, ...) (bookid, contributorid, functionid)

contract : contrat (contractid, contributorid, functionid)

contractedition : n éditions sur un contrat (courante, poche, ...) (editionid, contractid)

contracteditionbook : n projets sur une édition (projet principal + éventuellement des "nouvelles éditions") (editionid, bookid)

Contrainte : les champs contributorid et functionid de la table contract doivent correspondrent aux champs contributorid et functionid de la table bookcontributor (en passant par contractedition et contracteditionbook).

Le principe de la requête : récupérer tous les projets sans contrat (donc sans édition)

Je ne sais pas si tout ça éclaire un petit peu le problème.

De mon côté, j'ai pris le parti faire le traitement par programmation (java) (en récupérant tous les projets, tous les contrats et en vérifiant les projets qu'on ne retrouve pas dans les contrats).
Je règle ainsi mon problème de performance mais c'était pour moi un petit enjeu de pouvoir le faire uniquement par requête...
22/11/2010, 14h48
CinePhil

Citation:

bookcontributor : n contributeurs d'un livre avec fonction (ex. auteur, illustrateur, ...) (bookid, contributorid, functionid)

Je comprends qu'il y a le morceau de MCD suivant :
book -1,n----bookcontributor----0,n- contributor
function -0,n----------|

bookcontributor serait une table associative issue d'une association ternaire du MCD.

Citation:

contract : contrat (contractid, contributorid, functionid)

Là j'ai un peu plus de mal.
Il semble que cette table soit issue d'une entité du MCD puisqu'elle a son identifiant propre et que cette entité ait 2 associations selon ce schéma :
contributor -0,n----avoir----1,1- contract -1,1----concerner----0,n- fonction

Ce qui m'étonne un peu ici, c'est que le contrat ne soit pas lié à un livre mais peut-être est-ce normal.

Citation:

contractedition : n éditions sur un contrat (courante, poche, ...) (editionid, contractid)

OK là c'est facile :
contract -1,n----contractedition----0,n- edition

Ceci dit, je n'ai pas vu la table edition dans la requête mais peut-être n'y en a t-il pas besoin.

Citation:

contracteditionbook : n projets sur une édition (projet principal + éventuellement des "nouvelles éditions") (editionid, bookid)

J'ai de nouveau du mal !

Citation:

Contrainte : les champs contributorid et functionid de la table contract doivent correspondrent aux champs contributorid et functionid de la table bookcontributor (en passant par contractedition et contracteditionbook).

Et là je ne retrouve plus le cheminement !

Tu n'aurais pas le schéma de la BDD ?

Citation:

De mon côté, j'ai pris le parti faire le traitement par programmation (java) (en récupérant tous les projets, tous les contrats et en vérifiant les projets qu'on ne retrouve pas dans les contrats).
Je règle ainsi mon problème de performance mais c'était pour moi un petit enjeu de pouvoir le faire uniquement par requête...

Et le jour où il y aura des milliers de livres avec encore plus de milliers d'éditions et de contributeurs, tu verras les performances de l'appli se dégrader rapidement, surtout s'il y a beaucoup d'utilisateurs simultanés, parce que tu demandes à une application Java de faire le boulot d'un SGBD !

Je t'encourage fortement à insister sur la résolution du problème en SQL.

Sans schéma de la BDD, je vais avoir du mal à t'aider davantage.
22/11/2010, 15h20
jgfa9

1 pièce(s) jointe(s)

Encore merci pour ta réponse.
J'aimerais bien effectivement résoudre ce problème...
En pièce jointe un schéma des tables utilisées pour la requête, en espérant qu'il sera assez clair et que ça pourra aider...
22/11/2010, 15h40
CinePhil

Quand tu dis ceci :

Citation:

Le principe de la requête : récupérer tous les projets sans contrat (donc sans édition)

Tu veux dire les livres (book) sans contrat ?
22/11/2010, 15h56
jgfa9

Citation:

Tu veux dire les livres (book) sans contrat ?

Oui, c'est bien ça !
22/11/2010, 16h03
CinePhil
Une réponse simple : comme dans la clé primaire de bookcontributor il y a à la fois bookid d'une part, contributorid et functionid qui proviennent de contract d'autre part, ne suffit-il pas de faire cette requête ?
Code:

1 2 3 4 SELECT b.bookid FROM book AS b LEFT OUTER JOIN bookcontributor bc ON bc.bookid = b.bookid WHERE bc.bookid IS NULL
22/11/2010, 16h17
jgfa9
Non, parce que justement, il n'existe pas nécessairement de contrat pour un livre.
La requête proposée ne retournerait que les contributeurs non affectés à un livre, ce qui fonctionnellement ne peut pas arriver.
Voici les processus :
- on créé d'abord une fiche livre (book) à laquelle on affecte un ou plusieurs contributeurs (bookcontributor).
- plus tard, on créé (éventuellement) un contrat (contract) pour un contributeur avec une fonction donnée.
- on "rattache" ensuite à ce contrat différentes éditions (contractedition).
- Enfin, à chaque édition, on affecte un ou plusieurs livres (en fonction de la combinaison (contributeur et fonction) spécifiée sur le contrat).
Le but de cette requête est de vérifier que tous les contrats ont bien été saisis, donc de s'assurer pour chaque contributeur/fonction d'un livre qu'il existe bien dans la base un contrat.

Ton schéma de données n'est de toute évidence pas complet ; tu as une table function et une table contributor.

Citation:

on créé d'abord une fiche livre (book) à laquelle on affecte un ou plusieurs contributeurs (bookcontributor).

D'après ton texte, voici le MCD :
book -1,n----bookcontributor----0,n- contributor

D'après la clé primaire de la table bookcontributor, dans cette association intervient également function :
book -1,n----bookcontributor----0,n- contributor
function -0,n----------|

Tables :
book (book_id...)
contributor (contributorid...)
function (functionid)
bookcontributor (bookid, contributorid, functionid...)

Citation:

plus tard, on créé (éventuellement) un contrat (contract) pour un contributeur avec une fonction donnée.

D'après cette phrase, ceci est pour le moment indépendant des livres.
contributor -0,n----avoir----1,1- contract -1,1----concerner----0,n- function

Tables :
contributor (contributorid...)
function (functionid)
contract (contractid, contributorid, function_d...)

Jusque là, ce sont les deux MCD que j'ai donnés dans un précédent message.

Citation:

on "rattache" ensuite à ce contrat différentes éditions (contractedition).

La phrase semblerait vouloir dire ceci :
contract -0,n----contratedition----0,n- edition

Mais la table de ton schéma semble plutôt signifier ceci :
contract -0,n----avoir----1,1- contratedition

Tables :
contract (contractid, contributorid, function_d...)
contractedition (editionid, contractid...)

Citation:

Enfin, à chaque édition, on affecte un ou plusieurs livres (en fonction de la combinaison (contributeur et fonction) spécifiée sur le contrat).

Il y a deux choses dans cette phrase :
1)

Citation:

- à chaque édition, on affecte un ou plusieurs livres

contratedition -0,n----concerner----0,n- book

Tables :
book (bookid...)
contractedition (editionid, contractid...)
bookcontractedition (bookid, editionid...)

2)

Citation:

(en fonction de la combinaison (contributeur et fonction) spécifiée sur le contrat)

C'est une contrainte sur les données qu'il faut gérer par un trigger. Ce n'est normalement pas directement modélisable.

Dans un bon MCD, il faut transformer l'association bookcontributor en entité, que j'appellerais alors contribution, selon ce schéma :
book -1,n----concerner----(1,1)- contribution -(1,1)----contribuer----0,n- contributor
function -0,n---appliquer----------(1,1)-|

Du fait de l'identification relative, marqué par les cardinalités entre parenthèses, on obtient la même table que bookcontributor avec une clé primaire triple, ce qui permet, comme tu l'as fait dans ton schéma, d'associer cette entité/table aux autres entités/tables.

Le principe de l'identification relative aurait dû aussi être employé pour cette association :
contract -0,n----avoir----(1,1)- contratedition

Et la table résultante pour l'édition récupère contract_id dans sa clé primaire :
contractedition (contractid, editionid...)

Dès lors, on peut transformer cette association :
contractedition -0,n----concerner----0,n- book
en celle-ci :
contractedition -0,n----concerner----0,n- bookcontributor

Tables :
contractedition (contractid, editionid...)
bookcontributor (bookid, contributorid, functionid...)
contracteditionbookcontributor (contractid, editionid, bookid, contributorid, functionid...)

Eh oui ! 5 colonnes forment la clé primaire !

Et dans tout ce que je viens de décrire, il n'y a pas d'association entre contract et bookcontributor, ce serait redondant avec contracteditionbookcontributor.

Revenons au besoin, que l'on peut maintenant, je pense, traduire par :
Quel sont les contributions sans édition de contrat ?
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 29 30 31 32 33 34 35 SELECT b.title, c.name AS collectionname, a.gencod, bc.contributorid, ctr.name AS contributorname, ctr.firstname AS contributorfirstname, f.functionid, f.name AS functionname, CASE WHEN b.firstpublicationdate <> '' THEN DATE_FORMAT(STR_TO_DATE(CONCAT('1/',b.firstpublicationdate),'%d/%m/%Y'),'%d/%m/%Y') ELSE '' END AS firstpublicationdate, b.monthpos, b.yearpos FROM bookcontributor AS bc INNER JOIN book AS b ON b.bookid = bc.bookid INNER JOIN collection AS c ON c.collectionid = b.collectionid INNER JOIN article AS a ON b.bookid = a.bookid INNER JOIN contributor AS ctr ON ctr.contributorid = bc.contributorid INNER JOIN function AS f ON f.functionid = bc.functionid LEFT OUTER JOIN contracteditionbookcontributor AS cebc ON cebc.bookid = bc.bookid AND cebc.contributorid = bc.contributorid AND cebc.functionid = bc.functionid WHERE cebc.bookid IS NULL AND b.projecttype <> 'PLV' AND b.projecttype <> 'ASSORT' AND b.projecttype <> 'PACK' AND b.houseid = 1 AND CASE WHEN b.firstpublicationdate <> '' THEN DATEDIFF(DATE_FORMAT(STR_TO_DATE(CONCAT('1/', b.firstpublicationdate), '%d/%m/%Y'), '%Y-%m-%d'), '2010-09-30') <= 0 ELSE DATEDIFF(DATE_FORMAT(STR_TO_DATE(CONCAT('1/',b.monthpos,'/', b.yearpos), '%d/%m/%Y'), '%Y-%m-%d'), '2010-09-30') <= 0 END

22/11/2010, 17h33
jgfa9

Merci encore pour ton implication !

Tu me conseilles donc de "fusionner" les tables "contracteditionbook" (editionid, bookid) et "contractedition" (contractid,editionid) pour former "contracteditionbookcontributor" (et en y ajoutant les clés contributorid et functionid), au moment où l'on renseigne un livre (book) sur une édition c'est bien ça ?
Ou bien est-ce une table supplémentaire ?

Si je comprends toujours bien, mieux vaut donc répéter dans une table des informations (ici contributorid et fonctionid) pour faciliter les requêtes plutôt qu'avoir des jointures impossibles à réaliser ?

Si tel est bien le cas (ça me semble effectivement très sensé et très réalisable), je vais regarder comment le mettre en oeuvre. Encore mille merci !