Explain différent pour 2 tables de même structure

Version imprimable

Bonjour,
les tables invoice2010 et invoice2011 ont exactement la même structure (mêmes champs et mêmes index). Dans ce cas comment se fait-il que les résultats d'un explain d'une requête sur chacune de ces tables soient différents ?
Code:

1 2 3 4 5 6 7 8 9 10 EXPLAIN SELECT allarticle.collectionid, invoicetable.clientid, invoicetable.flowtypecode, SUM(invoicetable.quantity), SUM(invoicetable.grossamount) FROM invoice2010 invoicetable JOIN allarticle ON (invoicetable.articleid = allarticle.articleid) WHERE flowtypecode IN ('O','V','R') GROUP BY collectionid, clientid, flowtypecode;
donne :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 ligne 1 id : 1 select_type : SIMPLE table : allarticle type : index possible_keys : PRIMARY key : allarticle$collectionid key_len : 4 ref : null rows : 25352 Extra : Using index; Using temporary; Using filesort ligne 2 id : 1 select_type : SIMPLE table : invoicetable type : ref possible_keys : invoice2010$articleid,invoice2010$flowtypecode key : invoice2010$articleid key_len : 4 ref : allarticle.articleid rows : 88 Extra : Using where
temps d'exécution : 195,79 s (!)

alors que
Code:

1 2 3 4 5 6 7 8 9 10 EXPLAIN SELECT allarticle.collectionid, invoicetable.clientid, invoicetable.flowtypecode, SUM(invoicetable.quantity), SUM(invoicetable.grossamount) FROM invoice2011 invoicetable JOIN allarticle ON (invoicetable.articleid = allarticle.articleid) WHERE flowtypecode IN ('O','V','R') GROUP BY collectionid, clientid, flowtypecode;
donne :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 ligne 1 id : 1 select_type : SIMPLE table : invoicetable type : ALL possible_keys : invoice2011$articleid,invoice2011$flowtypecode key : null key_len : null ref : null rows : 3105041 Extra : Using index; Using temporary; Using filesort ligne 2 id : 1 select_type : SIMPLE table : allarticle type : eq_ref possible_keys : PRIMARY key : PRIMARY key_len : 4 ref : invoicetable.articleid rows : 1 Extra :
temps d'exécution : 28,63 s (!)

La table invoice2010 comporte 3.010.195 enregistrements et la table invoice2011 en comporte 3.104.783

Les index des tables invoice2010 et invoice2011 sont articleid, clientid, flowtypecode et month.

La table allarticle comporte 25.197 enregistrements.

Pourquoi une telle différence ? Et comment faire pour que la première requête se comporte comme la seconde ?

Merci pour votre aide.

29/08/2012, 12h12
jgfa9

C'est très étrange : la suppression des tous les index de la table invoice2010 puis leur recréation a paru corriger le problème mais après exécution de la requête, le problème est réapparu !!
Est-ce que quelqu'un a une idée de ce qui peut bien se passer ?
Merci.
29/08/2012, 14h01
CinePhil

J'ose espérer que la colonne allarticle.collectionid est indexée ?
C'est le premier critère de groupage !

On peut avoir la structure des tables (résultat complet de SHOW CREATE TABLE la_table) ?

Bonjour CinePhil ! Merci pour ta réponse.
Oui, bien sûr, la colonne allarticle.collectionid est indexée !

Voici la structure des tables "invoice" :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE  `invoice2010` (
  `invoiceid` int(10) unsigned NOT NULL,
  `gencod` varchar(13) DEFAULT NULL,
  `articleid` int(10) unsigned NOT NULL,
  `year` int(10) unsigned NOT NULL,
  `month` int(10) unsigned NOT NULL,
  `clientcode` varchar(8) NOT NULL,
  `clientid` int(10) unsigned NOT NULL,
  `flowtypecode` varchar(1) DEFAULT NULL,
  `selltypecode` varchar(2) DEFAULT NULL,
  `quantity` int(10) DEFAULT NULL,
  `grossamount` decimal(10,2) DEFAULT NULL,
  `netamount` decimal(10,2) DEFAULT NULL,
  `discountrate` decimal(10,4) NOT NULL,
  PRIMARY KEY (`invoiceid`),
  KEY `invoice2010$articleid` (`articleid`) USING BTREE,
  KEY `invoice2010$clientid` (`clientid`),
  KEY `invoice2010$flowtypecode` (`flowtypecode`),
  KEY `invoice2010$month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE  `invoice2011` (
  `invoiceid` int(10) unsigned NOT NULL,
  `gencod` varchar(13) DEFAULT NULL,
  `articleid` int(10) unsigned NOT NULL,
  `year` int(10) unsigned NOT NULL,
  `month` int(10) unsigned NOT NULL,
  `clientcode` varchar(8) NOT NULL,
  `clientid` int(10) unsigned NOT NULL,
  `flowtypecode` varchar(1) DEFAULT NULL,
  `selltypecode` varchar(2) DEFAULT NULL,
  `quantity` int(10) DEFAULT NULL,
  `grossamount` decimal(10,2) DEFAULT NULL,
  `netamount` decimal(10,2) DEFAULT NULL,
  `discountrate` decimal(10,4) NOT NULL,
  PRIMARY KEY (`invoiceid`),
  KEY `invoice2011$articleid` (`articleid`) USING BTREE,
  KEY `invoice2011$clientid` (`clientid`),
  KEY `invoice2011$flowtypecode` (`flowtypecode`),
  KEY `invoice2011$month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

et "allarticle"

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE  `alize20120625`.`allarticle` (
  `articleid` int(10) unsigned NOT NULL,
  `houseid` int(10) unsigned NOT NULL,
  `housename` varchar(100) NOT NULL,
  `groupid` int(10) unsigned NOT NULL,
  `groupname` varchar(100) NOT NULL,
  `collectionid` int(10) unsigned NOT NULL,
  `collectionname` varchar(100) NOT NULL,
  `title` varchar(200) NOT NULL,
  `contributorname` varchar(200) NOT NULL,
  `gencod` varchar(13) NOT NULL,
  `bookid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`articleid`),
  KEY `allarticle$collectionid` (`collectionid`),
  KEY `allarticle$groupid` (`groupid`),
  KEY `allarticle$houseid` (`houseid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Merci pour ton aide.

29/08/2012, 14h30
CinePhil

En dehors du fait choquant de voir housename, groupname, collectionname dans la table allarticle alors qu'y figurent déjà leurs identifiants, je ne vois rien d'anormal dans les tables ou d'ambiguïtés possibles dans les noms de colonnes dans les requêtes.
Cependant, je te conseille quand même de toujours préfixer les colonnes avec l'alias de la table dès qu'il y a plus d'une table dans la requête.

Quelques pistes pour ton problème...
1) As-tu essayé de lancer les requêtes d'abord la 2010 puis la 2011 et d'abord la 2011 puis la 2010 ?

2) Ce qui est bizarre, c'est que dans le premier EXPLAIN, la table allarticle est parcourue entièrement (rows : 25352) alors que dans l'autre l'index y est beaucoup plus efficace (rows : 1, ce qui d'ailleurs très peu !).
D'ailleurs, dans les deux cas il n'utilise pas le même index.

Y a t-il une répartition équivalente des lignes parmi les critères de groupage entre les deux années ?
=> Ajoute un COUNT à la requête pour voir ça.

3) Par contre, dans le premier EXPLAIN, invoicetable utilise l'index articleid et ne retourne que 88 lignes alors que dans le second la table est parcourue en entier !

Combien de lignes retourne chaque requête ?
29/08/2012, 15h02
Fred_34

Essaie de remplacer "join" par "straight_join", je ne l'ai jamais utilisé, mais j'ai l'impression qu'il est fait pour ça.
29/08/2012, 16h02
jgfa9

"straight_join" fonctionne parfaitement, merci.

Pour l'éclairage de ce "dysfonctionnement", la requête sur 2010 donne 368.778 enregistrements et sur 2011, 376.680.

La répartition est équivalente : entre 1 et 5000 lignes regroupées.

Du coup, la recommandation est-elle de toujours utiliser "straight_join" plutôt que "join" ? Pourquoi MySql ne trouve-t-il pas tout seul l'ordre de jointure idéal pour optimiser cette requête (on passe quand même de 27 à près de 200 s) ?

Encore merci.
29/08/2012, 16h16
CinePhil

Citation:

Envoyé par Doc MySQL

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

Encore un paliatif à une déficience de MySQL on dirait ! :roll:

Sauf erreur de ma part, STRAIT JOIN n'est pas dans le standard SQL. Je ne l'ai pas trouvé dans le livre de SQLPro en tout cas !

Ceci étant dit, je reste dubitatif sur le temps d'exécution des requêtes. La machine qui les exécute ne serait-elle pas un peu sous-dimensionnée ?
J'ai eu des résultats bien plus rapides avec des tables beaucoup plus grosses sur un serveur normal d'il y a quelques années.
29/08/2012, 17h44
skuatamad

Tu peux utiliser ANALYZE pour recalculer les stats de la table invoice2010 qui semblent mauvaises.

Par ailleurs tu peux utiliser PROFILE pour mieux déterminer d'où proviennent les lenteurs :
http://dev.mysql.com/tech-resources/...tic_tools.html