Optimisation requête IN (SELECT GROUP BY)
Bonjour,
la requête suivante prend trop de temps (plus d'une nuit) :
Code:
1
2
3
4
5
6
7
8
9
10
| SELECT DISTINCT vaa.Employe_CodeEmploye, vaa.AffStruct_NomCourt, vaa.AffStruct_Nom,
vaa.AffStruct_Type, vaa.AffStruct_ID, vaa.AFFSTRUCT_BRISDEGLACE,
vaa.AffStruct_Debut, vaa.AffStruct_Fin
FROM vue_aff_actives as vaa
WHERE vaa.AffStruct_NomCourt
In (SELECT DISTINCT AffStruct_NomCourt
FROM vue_aff_actives As Tmp
where Tmp.AffStruct_Type = vaa.AffStruct_Type And Tmp.Employe_CodeEmploye = vaa.Employe_CodeEmploye
GROUP BY Tmp.AffStruct_NomCourt, Tmp.AffStruct_Type, Tmp.Employe_CodeEmploye
HAVING Count(*)>1 ) |
Son explain plan est :
Citation:
id * select_type * table type possible_keys key key_len ref rows Extra *
1 PRIMARY table_emp ALL IDX_ORE_EMPLOYE_ID,IDX_ORE_EMPLOYE_FIN 43035 Using where; Using temporary
1 PRIMARY table_aff ref IDX_ORA_AFFSTRUCT_NOMCOURT,IDX_ORA_AFFSTRUCT_FIN,IDX_ORA_EMPLOYE_ID,IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE IDX_ORA_EMPLOYE_ID 23 vichus.table_emp.EMPLOYE_ID 22 Using where
2 DEPENDENT SUBQUERY table_aff range IDX_ORA_AFFSTRUCT_NOMCOURT,IDX_ORA_AFFSTRUCT_FIN,IDX_ORA_AFFSTRUCT_TYPE,IDX_ORA_EMPLOYE_ID,IDX_ORA_EMPLOYE_CODEEMPLOYE,IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE 103 567484 Using where; Using temporary; Using filesort
2 DEPENDENT SUBQUERY table_emp ref IDX_ORE_EMPLOYE_ID,IDX_ORE_EMPLOYE_FIN IDX_ORE_EMPLOYE_ID 23 vichus.table_aff.EMPLOYE_ID 1 Using where; Distinct
La vue est la suivante :
Code:
1
2
3
4
5
6
| CREATE OR REPLACE VIEW vue_aff_actives AS
SELECT table_aff.*
FROM table_aff INNER JOIN table_emp ON table_aff.Employe_ID = table_emp.Employe_ID
WHERE table_aff.AffStruct_NomCourt Is Not Null AND
(table_aff.AffStruct_Fin Is Null Or table_aff.AffStruct_Fin > Now()) AND
(table_emp.Employe_Fin Is Null Or table_emp.Employe_Fin > Now()); |
La sous-requête en précisant les variables prend 42 secondes et donne 9500 résultats :
Code:
1
2
3
4
5
| SELECT DISTINCT AffStruct_NomCourt, AffStruct_Type, Employe_CodeEmploye
FROM vue_aff_actives As Tmp
where Tmp.AffStruct_Type = 'Unité XXX' And Tmp.Employe_CodeEmploye = '1111111'
GROUP BY Tmp.AffStruct_NomCourt, Tmp.AffStruct_Type, Tmp.Employe_CodeEmploye
HAVING Count(*)>1 |
Et la requête en précisant les variables dans le WHERE prend 2 minutes 14, ce qui même en ne multipliant que par 1000 (au lieu de 9500) est beaucoup trop.
Code:
1
2
| ... and vaa.AffStruct_Type = 'Unité XXX'
and vaa.Employe_CodeEmploye = '1111111'; |
La table principale est comme ci-dessous :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| create table table_aff(
AFFSTRUCT_ID int,
AFFSTRUCT_NOMCOURT varchar(100),
AFFSTRUCT_NOM varchar(100),
AFFSTRUCT_BRISDEGLACE varchar(100),
AFFSTRUCT_DEBUT datetime,
AFFSTRUCT_FIN datetime,
AFFSTRUCT_TYPE varchar(100),
EMPLOYE_ID varchar(20),
EMPLOYE_CODEEMPLOYE varchar(100),
ORA_ID int not null auto_increment,
primary key(ORA_ID),
KEY IDX_ORA_AFFSTRUCT_ID (AFFSTRUCT_ID),
KEY IDX_ORA_AFFSTRUCT_NOMCOURT (AFFSTRUCT_NOMCOURT),
KEY IDX_ORA_AFFSTRUCT_FIN (AFFSTRUCT_FIN),
KEY IDX_ORA_AFFSTRUCT_TYPE (AFFSTRUCT_TYPE),
KEY IDX_ORA_EMPLOYE_ID (EMPLOYE_ID),
KEY IDX_ORA_EMPLOYE_CODEEMPLOYE (EMPLOYE_CODEEMPLOYE),
KEY IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE (AFFSTRUCT_NOMCOURT, AFFSTRUCT_TYPE, EMPLOYE_CODEEMPLOYE)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Avez-vous une idée d'amélioration possible ?
