how to avoid sub-query to gain performance
am 02.06.2010 16:28:07 von Lin Chun--0016e6dd8bc4111a5704880ce693
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
*hi*
*
*
*i have a reporting query which have 2 long sub-query*
SELECT r1.code_centre, r1.libelle_centre, r1.id_equipe, r1.equipe,
r1.id_file_attente,
r1.libelle_file_attente,r1.id_date, r1.tranche,
r1.id_granularite_de_periode,r1.granularite,
r1.ContactsTraites, r1.ContactsenParcage, r1.ContactsenComm,
r1.DureeTraitementContacts,
r1.DureeComm, r1.DureeParcage, r2.AgentsConnectes, r2.DureeConnexion,
r2.DureeTraitementAgents,
r2.DureePostTraitement
FROM
( SELECT cc.id_centre_contact, cc.code_centre, cc.libelle_centre,
a.id_equipe, a.equipe,
a.id_file_attente, f.libelle_file_attente, a.id_date, g.tranche,
g.id_granularite_de_periode,
g.granularite, sum(Nb_Contacts_Traites) as ContactsTraites,
sum(Nb_Contacts_en_Parcage) as ContactsenParcage,
sum(Nb_Contacts_en_Communication) as ContactsenComm,
sum(Duree_Traitement/1000) as DureeTraitementContacts,
sum(Duree_Communication / 1000 + Duree_Conference / 1000 +
Duree_Com_Interagent / 1000) as DureeComm,
sum(Duree_Parcage/1000) as DureeParcage
FROM agr_synthese_activite_media_fa_agent a, centre_contact cc,
direction_contact dc, granularite_de_periode g, media m, file_attent=
e
f
WHERE m.id_media =3D a.id_media
AND cc.id_centre_contact =3D a.id_centre_contact
AND a.id_direction_contact =3D dc.id_direction_contact
AND dc.direction_contact =3D'INCOMING'
AND a.id_file_attente =3D f.id_file_attente
AND m.media =3D 'PHONE'
AND ( ( g.valeur_min =3D date_format(a.id_date,'%d/%m') and
g.granularite =3D 'Jour')
or ( g.granularite =3D 'Heure' and a.id_th_heure =3D
g.id_granularite_de_periode) )
GROUP by cc.id_centre_contact, a.id_equipe, a.id_file_attente,
a.id_date, g.tranche,
g.id_granularite_de_periode) r1,
(
(SELECT cc.id_centre_contact,cc.code_centre, cc.libelle_centre,
a.id_equipe, a.equipe,
a.id_date, g.tranche, g.id_granularite_de_periode,g.granularite,
count(distinct a.id_agent) as AgentsConnectes,
sum(Duree_Connexion / 1000) as DureeConnexion,
sum(Duree_en_Traitement / 1000) as DureeTraitementAgents,
sum(Duree_en_PostTraitement / 1000) as DureePostTraitement
FROM activite_agent a, centre_contact cc, granularite_de_periode =
g
WHERE ( g.valeur_min =3D date_format(a.id_date,'%d/%m') and
g.granularite =3D 'Jour')
AND cc.id_centre_contact =3D a.id_centre_contact
GROUP BY cc.id_centre_contact, a.id_equipe, a.id_date, g.tranche=
,
g.id_granularite_de_periode )
UNION
(SELECT cc.id_centre_contact,cc.code_centre, cc.libelle_centre,
a.id_equipe, a.equipe,
a.id_date, g.tranche, g.id_granularite_de_periode,g.granularite,
count(distinct a.id_agent) as AgentsConnectes,
sum(Duree_Connexion / 1000) as DureeConnexion,
sum(Duree_en_Traitement / 1000) as DureeTraitementAgents,
sum(Duree_en_PostTraitement / 1000) as DureePostTraitement
FROM activite_agent a, centre_contact cc, granularite_de_periode g
WHERE ( g.granularite =3D 'Heure'
AND a.id_th_heure =3D g.id_granularite_de_periode)
AND cc.id_centre_contact =3D a.id_centre_contact
GROUP BY cc.id_centre_contact,a.id_equipe, a.id_date, g.tranche,
g.id_granularite_de_periode)
) r2
WHERE r1.id_centre_contact =3D r2.id_centre_contact
AND r1.id_equipe =3D r2.id_equipe AND r1.id_date =3D r2.id_date
AND r1.tranche =3D r2.tranche AND r1.id_granularite_de_periode =3D
r2.id_granularite_de_periode
GROUP BY r1.id_centre_contact , r1.id_equipe, r1.id_file_attente,
r1.id_date, r1.tranche, r1.id_granularite_de_periode
ORDER BY r1.code_centre, r1.libelle_centre, r1.equipe,
r1.libelle_file_attente, r1.id_date, r1.id_granularite_de_periode,r1.tranch=
e
*the EXPLAIN shows*
| id | select_type | table | type| possible_keys | key | key_len | ref|
rows | Extra |
'1', 'PRIMARY', '
temporary; Using filesort'
'1', 'PRIMARY', '
where; Using join buffer'
'3', 'DERIVED', 'a', 'ALL', 'fk_Activite_Agent_centre_contact', NULL, NULL,
NULL, '83433', 'Using temporary; Using filesort'
'3', 'DERIVED', 'g', 'ref', 'Index_granularite,Index_Valeur_min',
'Index_Valeur_min', '23', 'func', '1', 'Using where'
'3', 'DERIVED', 'cc', 'ALL', 'PRIMARY', NULL, NULL, NULL, '6', 'Using where=
;
Using join buffer'
'4', 'UNION', 'g', 'ref', 'PRIMARY,Index_granularite', 'Index_granularite',
'23', '', '24', 'Using where; Using temporary; Using filesort'
'4', 'UNION', 'a', 'ref',
'fk_Activite_Agent_centre_contact,fk_activite_agent_TH_heure ',
'fk_activite_agent_TH_heure', '5',
'reporting_acd.g.Id_Granularite_de_periode', '2979', 'Using where'
'4', 'UNION', 'cc', 'ALL', 'PRIMARY', NULL, NULL, NULL, '6', 'Using where;
Using join buffer'
NULL, 'UNION RESULT', '
'2', 'DERIVED', 'g', 'range', 'PRIMARY,Index_granularite,Index_Valeur_min',
'Index_granularite', '23', NULL, '389', 'Using where; Using temporary; Usin=
g
filesort'
'2', 'DERIVED', 'a', 'ALL',
'fk_agr_synthese_activite_media_fa_agent_centre_contact,fk_a gr_synthese_act=
ivite_media_fa_agent_direction_contact,fk_agr_synthese_activ ite_media_fa_ag=
ent_file_attente,fk_agr_synthese_activite_media_fa_agent_med ia,fk_agr_synth=
ese_activite_media_fa_agent_th_heure',
NULL, NULL, NULL, '20903', 'Using where; Using join buffer'
'2', 'DERIVED', 'cc', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'reporting_acd.a.Id_Centre_Contact', '1', ''
'2', 'DERIVED', 'f', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'reporting_acd.a.Id_File_Attente', '1', ''
'2', 'DERIVED', 'dc', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'reporting_acd.a.Id_Direction_Contact', '1', 'Using where'
'2', 'DERIVED', 'm', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'reporting_acd.a.Id_Media', '1', 'Using where'
*don't know it very clear, but i think is the problem of
take full scaning*
*
*
*than i change all the sub-query to views(create view as select sub-query),
and the result is the same*
*thanks for any advice*
--=20
-------------------------
Lin Chun
--0016e6dd8bc4111a5704880ce693--