Bonjour,
J'ai la requête suivante :
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SELECT SUM(IF(flowtypecode IN ('V','O','R'),quantityN1,0)) AS quantityN1,
        SUM(IF(flowtypecode IN ('V','O','R'),quantityN,0)) AS quantityN,
        SUM(IF(flowtypecode IN ('V','O','R'),amountN1,0)) AS amountN1,
        SUM(IF(flowtypecode IN ('V','O','R'),amountN,0)) AS amountN,
        SUM(IF(flowtypecode='R',quantityN,0)) AS returnquantity,
        SUM(IF(flowtypecode IN ('V','O'),quantityN,0)) AS sellquantity,
        SUM(IF(flowtypecode='R',amountN,0)) AS returnamount,
        SUM(IF(flowtypecode IN ('V','O'),amountN,0)) AS sellamount,
        signtypeid,
        signid,
        salepointid
 FROM (SELECT tableN1.quantity AS quantityN1,
              0 AS quantityN,
              tableN1.amount AS amountN1,
              0 AS amountN,
              tableN1.flowtypecode,
              c.signtypeid,
              c.signid,
              c.salepointid
      FROM invoice2011collectionclient tableN1 JOIN client c ON (tableN1.clientid=c.clientid)
      WHERE tableN1.flowtypecode IN ('O','V','R')
      AND tableN1.month BETWEEN 1 AND 8
      UNION ALL
      SELECT 0 AS quantityN1,
             tableN.quantity AS quantityN,
             0 AS amountN1,
             tableN.amount AS amountN,
             tableN.flowtypecode,
             c.signtypeid,
             c.signid,
             c.salepointid
      FROM invoicecollectionclient tableN JOIN client c ON (tableN.clientid=c.clientid)
      WHERE tableN.flowtypecode IN ('O','V','R')
      AND tableN.month BETWEEN 1 AND 8
      UNION ALL
      SELECT 0 AS quantityN1,
             deliveryorder.quantity AS quantityN,
             0 AS amountN1,
             deliveryorder.amount AS amountN,
             deliveryorder.flowtypecode,
             c.signtypeid,
             c.signid,
             c.salepointid
      FROM deliveryordercollectionclient deliveryorder JOIN client c ON (deliveryorder.clientid=c.clientid)
      WHERE deliveryorder.flowtypecode IN ('O','V','R')
      AND deliveryorder.month <= 8) AS mytable
 GROUP BY salepointid
Les tables invoice2011collectionclient, invoicecollectionclient et deliveryordercollectionclient ont toutes la même structure :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
CREATE TABLE  `invoice2011collectionclient` (
  `collectionid` int(10) unsigned NOT NULL,
  `clientid` int(10) unsigned NOT NULL,
  `flowtypecode` varchar(2) NOT NULL,
  `month` int(10) unsigned NOT NULL,
  `quantity` int(10) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`collectionid`,`clientid`,`flowtypecode`,`month`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
la table client :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE  `client` (
  `clientid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clientcode` varchar(13) NOT NULL,  
  `salepointid` int(10) unsigned NOT NULL,
  `signid` int(10) unsigned NOT NULL DEFAULT '0',
  `signtypeid` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`clientid`),
  KEY `client$salepointid` (`salepointid`),
  KEY `client$signid` (`signid`),
  KEY `client$signtypeid` (`signtypeid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Taille des tables :
invoice2011collectionclient : 1.112.014 enregistrements
invoicecollectionclient : 462.620 enregistrements
deliveryordercollectionclient : 85.411 enregistrements
client : 16.042 enregistrements

Je ne sais pas trop comment poster le résultat du EXPLAIN qui est assez conséquent (8 lignes)...

Cette requête prend 7,69 s à s'exécuter... Y-a-t'il moyen d'améliorer ce résultat ?
La requête est-elle mal "construite" ?
Faut-il ajouter des index ? En supprimer ?
Merci beaucoup pour vos indices.