List:General Discussion« Previous MessageNext Message »
From:Lin Chun Date:June 2 2010 2:28pm
Subject:how to avoid sub-query to gain performance
View as plain text  
*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_attente
f
    WHERE   m.id_media = a.id_media
    AND cc.id_centre_contact = a.id_centre_contact
    AND a.id_direction_contact = dc.id_direction_contact
    AND dc.direction_contact ='INCOMING'
    AND a.id_file_attente = f.id_file_attente
    AND m.media = 'PHONE'
    AND  (  ( g.valeur_min = date_format(a.id_date,'%d/%m') and
g.granularite = 'Jour')
    or ( g.granularite = 'Heure'   and  a.id_th_heure =
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 = date_format(a.id_date,'%d/%m') and
g.granularite = 'Jour')
        AND cc.id_centre_contact = 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 = 'Heure'
        AND  a.id_th_heure = g.id_granularite_de_periode)
        AND cc.id_centre_contact = 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 = r2.id_centre_contact
AND r1.id_equipe = r2.id_equipe    AND r1.id_date  = r2.id_date
AND r1.tranche = r2.tranche      AND r1.id_granularite_de_periode =
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.tranche

*the EXPLAIN shows*

| id | select_type  | table | type| possible_keys | key  | key_len | ref|
rows  | Extra                                        |
'1', 'PRIMARY', '<derived3>', 'ALL', NULL, NULL, NULL, NULL, '2520', 'Using
temporary; Using filesort'
'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '4378', 'Using
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', '<union3,4>', 'ALL', NULL, NULL, NULL, NULL, NULL, ''
'2', 'DERIVED', 'g', 'range', 'PRIMARY,Index_granularite,Index_Valeur_min',
'Index_granularite', '23', NULL, '389', 'Using where; Using temporary; Using
filesort'
'2', 'DERIVED', 'a', 'ALL',
'fk_agr_synthese_activite_media_fa_agent_centre_contact,fk_agr_synthese_activite_media_fa_agent_direction_contact,fk_agr_synthese_activite_media_fa_agent_file_attente,fk_agr_synthese_activite_media_fa_agent_media,fk_agr_synthese_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 <derived> seems it
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*


-- 
-------------------------
Lin Chun

Thread
how to avoid sub-query to gain performanceLin Chun2 Jun
  • Re: how to avoid sub-query to gain performancePerrin Harkins2 Jun