Optimiser un "GROUP BY HAVING COUNT"

Version imprimable

Bonjour,

le requête suivante prend plus de 2 heures, ce qui est un "peu" trop long. Table1 contient 110 000 lignes et table2 40 000.
Il y a un index sur chacun des champs (NomCourt, Type et CodeEmploye) ainsi qu'un index sur les trois réunis. Je ne vois pas comment améliorer les performances (autrement qu'en améliorant le serveur).
La requête liste les doublons.
Code:

1 2 3 4 5 6 7 8 9 10 SELECT a.CodeEmploye, a.Nom, a.Debut, a.Fin FROM table1 as a INNER JOIN table2 ON a.ID = table2.ID WHERE a.NomCourt In (SELECT NomCourt FROM table1 As Tmp GROUP BY Tmp.NomCourt, Tmp.Type, Tmp.CodeEmploye HAVING Count(*) > 1 And Tmp.Type = a.Type And Tmp.CodeEmploye = a.CodeEmploye) AND (table2.Fin Is Null Or table2.Fin > Now());

Filtrer dans le HAVING est une très mauvaise idée, d'autant que je doute que ça marche sur autre-chose que MySQL puisque ce sont des colonnes ne faisant pas partie du group by.

Essayez plutôt ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT a.CodeEmploye, a.Nom, a.Debut, a.Fin FROM table1 as a INNER JOIN table2 ON a.ID = table2.ID WHERE a.NomCourt In ( SELECT NomCourt FROM table1 As Tmp WHERE Tmp.Type = a.Type And Tmp.CodeEmploye = a.CodeEmploye GROUP BY Tmp.NomCourt, Tmp.Type, Tmp.CodeEmploye HAVING Count(*) > 1 ) AND (table2.Fin Is Null Or table2.Fin > Now());

22/09/2015, 14h14
escartefigue

Pour pouvoir répondre précisément, il faudrait que vous communiquiez le DDL des tables et index

Quelle est la clef unique de la table1 ? d'après votre requête, le code employé n'est pas unique dans table1 ? ni lie trio nomcourt+code employé + type ?

@StringBuilder
Cette requête provient d'Access. J'ai essayé en sortant les filtres de la clause HAVING, mais ça ne fonctionne pas mieux (j'ai arrêté la requête après 10 min.).

Si je comprends bien, l'explain plan indique un problème avec table2 alors qu'il y a des index sur les deux champs.

Code:

1
2
3
4
5
6
7
+----+--------------------+-----------+-------+---------------------------------------------------------------------------+-----------------------------------+---------+-----------+-------+--------------------------+
| id | select_type        | table     | type  | possible_keys                                                             | key                               | key_len | ref       | rows  | Extra                    |
+----+--------------------+-----------+-------+---------------------------------------------------------------------------+-----------------------------------+---------+-----------+-------+--------------------------+
|  1 | PRIMARY            | table2    | ALL   | IDX_ORE_ID,IDX_ORE_FIN                                                    | NULL                              | NULL    | NULL      | 41269 | Using where              |
|  1 | PRIMARY            | table1    | ref   | IDX_ORA_NOMCOURT,IDX_ORA_FIN,IDX_ORA_ID,IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE | IDX_ORA_ID                        | 23      | table2.ID |    10 | Using where              |
|  2 | DEPENDENT SUBQUERY | Tmp       | index | IDX_ORA_CODEEMPLOYE,IDX_ORA_TYPE                                          | IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE | 309     | NULL      |     1 | Using where; Using index |
+----+--------------------+-----------+-------+---------------------------------------------------------------------------+-----------------------------------+---------+-----------+-------+--------------------------+

Les DDL :

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
create table table1(
	ID int,
	NOMCOURT varchar(100),
	NOM varchar(100),
	BRISDEGLACE varchar(100),
	DEBUT datetime default 0,
	FIN datetime default 0,
	TYPE varchar(100),
	ID varchar(20),
	CODEEMPLOYE varchar(100),
	ORA_ID int not null auto_increment,
	primary key(ORA_ID),
	KEY IDX_ORA_ID (ID),
	KEY IDX_ORA_NOMCOURT (NOMCOURT),
	KEY IDX_ORA_FIN (FIN),
	KEY IDX_ORA_TYPE (TYPE),
	KEY IDX_ORA_ID (ID),
	KEY IDX_ORA_CODEEMPLOYE (CODEEMPLOYE),
	KEY IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE (NOMCOURT, TYPE, CODEEMPLOYE)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
create table table2(
	ID varchar(20),
	CODEEMPLOYE varchar(25),
	NOM varchar(50),
	PRENOM varchar(50),
	STATUT varchar(50),
	TITRE VARCHAR(3),
	DEBUT datetime default 0,
	FIN datetime default 0,
	ORE_ID int not null auto_increment,
	primary key(ORE_ID),
	KEY IDX_ORE_ID (ID),
	KEY IDX_ORE_CODEEMPLOYE (CODEEMPLOYE),
	KEY IDX_ORE_FIN (FIN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

22/09/2015, 15h05
escartefigue

il y a des aberrations dans le DDL

D'une part vous faites une jointure entre un varchar(20) et un integer :aie:, ce qui compromet l'usage d'index

Il est donc tout à fait normal que votre jointure rame !

d'autre part vous avez des redondances d'index et des index probablement inutiles

De plus, pour trouver les doubles vous n'avez nullement besoin de faire un groupage du coup, un where exists suffit en vérifant que l'id est différent !

Je suppose donc que l'id qui vous permet de faire la jointure devrait être de type integer dans les 2 tables, une fois que le DDL sera corrigé dans Table2, vous pourrez faire comme ci-dessous :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT a.CodeEmploye
      ,a.Nom 
      ,a.Debut
      ,a.Fin
FROM       table1 as a 
INNER JOIN table2 
   ON a.ID = table2.ID
WHERE exists
     (SELECT 1
      FROM  table1 As Tmp 
      WHERE Tmp.NomCourt    = a.NomCourt
        and Tmp.Type        = a.Type 
	and Tmp.CodeEmploye = a.CodeEmploye
        and Tmp.ORA_ID     <> a.ORA_ID)
  AND (table2.Fin Is Null Or table2.Fin > Now())
;

La jointure se fait bien sur deux varchar(20), c'est moi qui est fait une erreur en renommant les champs dans le ddl.

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
create table table1(
	AID int,
	NOMCOURT varchar(100),
	NOM varchar(100),
	BRISDEGLACE varchar(100),
	DEBUT datetime default 0,
	FIN datetime default 0,
	TYPE varchar(100),
	ID varchar(20),
	CODEEMPLOYE varchar(100),
	ORA_ID int not null auto_increment,
	primary key(ORA_ID),
	KEY IDX_ORA_ID (ID),
	KEY IDX_ORA_NOMCOURT (NOMCOURT),
	KEY IDX_ORA_FIN (FIN),
	KEY IDX_ORA_TYPE (TYPE),
	KEY IDX_ORA_ID (ID),
	KEY IDX_ORA_CODEEMPLOYE (CODEEMPLOYE),
	KEY IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE (NOMCOURT, TYPE, CODEEMPLOYE)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
create table table2(
	ID varchar(20),
	CODEEMPLOYE varchar(25),
	NOM varchar(50),
	PRENOM varchar(50),
	STATUT varchar(50),
	TITRE VARCHAR(3),
	DEBUT datetime default 0,
	FIN datetime default 0,
	ORE_ID int not null auto_increment,
	primary key(ORE_ID),
	KEY IDX_ORE_ID (ID),
	KEY IDX_ORE_CODEEMPLOYE (CODEEMPLOYE),
	KEY IDX_ORE_FIN (FIN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Je teste la requête proposée.

22/09/2015, 15h32
escartefigue

Si vous avez les habilitations pour le faire, profitez en pour
- supprimer l'index IDX_ORA_NOMCOURT (NOMCOURT) redondant avec IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE (NOMCOURT, TYPE, CODEEMPLOYE)
- vérifier combien il y a de valeurs distinctes possibles pour TYPE, s'il n'existe que 5 ou 6 valeurs possibles, alors vous pouvez supprimer l'index IDX_ORA_TYPE (TYPE) car inutile
- vérifier s'il y a un intérêt fonctionnel à rechercher sur la date de fin sans autre critère, ce qui est peu probable, dans le cas contraire, l'index IDX_ORA_FIN(FIN) peut être supprimé.
- pareil pour l'index IDX_ORE_FIN de la table2
- remplacer TITRE VARCHAR(3) par TITRE CHAR(3)
- mettre des valeurs par défaut qui soient des dates/heures dans vos colonnes date-time, je suis surpris que zéro soit accepté !
22/09/2015, 16h00
Christophe P.

Merci pour vos conseils. La requête s'effectue en 6 minutes (c'est un temps acceptable pour un script).