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

**jgfa9** · 29/08/2012, 11h59

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 :

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

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 :

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
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 :

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

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 :

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
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.

**jgfa9** · 29/08/2012, 12h12

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.

**CinePhil** · 29/08/2012, 14h01

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) ?

**jgfa9** · 29/08/2012, 14h09

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 :

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
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 :

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
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 :

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
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.

**CinePhil** · 29/08/2012, 14h30

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 ?

**Fred_34** · 29/08/2012, 15h02

Essaie de remplacer "join" par "straight_join", je ne l'ai jamais utilisé, mais j'ai l'impression qu'il est fait pour ça.

**jgfa9** · 29/08/2012, 16h02

"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.

**CinePhil** · 29/08/2012, 16h16

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 !

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.

**skuatamad** · 29/08/2012, 17h44

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

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

Requêtes MySQL

Discussions similaires

Partager

Partager