mysql How optimize query

How can i optimize this query ?

CREATE TABLE rechercherefppv3bis_tmp
( `id` varchar(25) KEY,   `dateMaj` datetime DEFAULT NULL,   `type` varchar(9) DEFAULT NULL,   `nom` varchar(60) DEFAULT NULL,   `prenom` varchar(150) DEFAULT NULL,   `adresseLigne2` varchar(38) DEFAULT NULL,   `adresseLigne3` varchar(38) DEFAULT NULL,   `adresseLigne4` varchar(38) DEFAULT NULL,   `adresseLigne5` varchar(38) DEFAULT NULL,   `adresseLigne6` varchar(38) DEFAULT NULL,   `adresseLigne7` varchar(38) DEFAULT NULL,   `emailPrincipal` varchar(255) DEFAULT NULL,   `telephonePrincipal` varchar(50) DEFAULT NULL,   `lignes4` text,   `lignes6` text,   `numeros` text,   `adresses` text,   `numProcuration` text,   `numReexpedition` text, `cp` varchar(255), `isDGP` boolean NULL, `civilite` varchar(255) NULL,`codeCivilite` int(10) NULL , `coclico` varchar(8) NULL , `certificationStatut` varchar(255) NULL , `siret` varchar(14) NULL , `nomSociete` varchar(255) NULL )
AS (
    SELECT
                    u.id,
                    str_to_date(GREATEST(u.dateMaj, ifnull(a.dateMaj, '1970-01-01 08:00:00'), ifnull(t.dateMaj, '1970-01-01 08:00:00'), ifnull(m.dateMaj, '1970-01-01 08:00:00')),'%Y-%m-%d %H:%i:%s') as dateMaj,
                    u.type as type,
                    u.nom as nom,
                    u.prenom as prenom,
                    a.ligne1 as adresseLigne1,
                    a.ligne2 as adresseLigne2,
                    a.ligne3 as adresseLigne3,
                    a.ligne4 as adresseLigne4,
                    a.ligne5 as adresseLigne5,
                    a.ligne6 as adresseLigne6,
                    a.ligne7 as adresseLigne7,
                    m.adresse as emailPrincipal,
                    t.numero AS telephonePrincipal,
                    GROUP_CONCAT(DISTINCT ads.ligne4 SEPARATOR ';') as lignes4,
                    GROUP_CONCAT(DISTINCT ads.ligne6 SEPARATOR ';') as lignes6,
                    GROUP_CONCAT(DISTINCT ts.numero SEPARATOR ';') as numeros,
                    GROUP_CONCAT(DISTINCT ms.adresse SEPARATOR ';') as adresses,
                    GROUP_CONCAT(DISTINCT pp.idOrigine SEPARATOR ';') as numProcuration,
                    GROUP_CONCAT(DISTINCT rc.numero SEPARATOR ';') as numReexpedition,
                    SUBSTRING_INDEX(a.ligne6,' ',1) AS cp,
                    case when ctp1.idContratDGP is null then false else true end as isDGP,
                    u.niveauAdhesion,
                    u.civilite,
                    u.codeCivilite,
                    u.certificationStatus as certificationStatut,
                    u.coclico,
                    cr.nom as nomSociete,
                    cr.siret as siret
                FROM ccu_user_v3bis  u
                    LEFT OUTER JOIN ccu_adresse_v3bis a ON a.idUser = u.id AND a.principale = 1
                    LEFT OUTER JOIN ccu_email_v3bis m ON m.idUser = u.id AND m.principal = 1
                    LEFT OUTER JOIN ccu_telephone_v3bis t ON t.idUser = u.id AND t.principal = 1
                    LEFT OUTER JOIN ccu_adresse_v3bis ads ON ads.idUser = u.id
                    LEFT OUTER JOIN ccu_email_v3bis ms ON ms.idUser = u.id
                    LEFT OUTER JOIN ccu_telephone_v3bis ts ON ts.idUser = u.id
                    LEFT OUTER JOIN procuration_part pp ON pp.idClient = u.id
                    LEFT OUTER JOIN reexp_contrat rc ON rc.idCCU = u.id
                    LEFT OUTER JOIN client_refpm cr ON cr.id = u.coclico
                    LEFT OUTER JOIN
                        (SELECT ctp.idCCUSouscripteur , max(ctp.id) as idContratDGP
                        FROM contrats_part ctp
                        WHERE ctp.idCCUSouscripteur is not null and ctp.source = 'Digiposte' and ctp.statutContrat = '2' GROUP BY ctp.idCCUSouscripteur)
                        as ctp1 ON ctp1.idCCUSouscripteur = u.id
                group by u.id
);

this query takes too long, I want to simplify it

Don't use "LEFT" unless the right-hand column is missing some rows and you want NULLs. In particular, contrats_part looks like it can never be null.
These indexes may help:

ctp:  INDEX(source, statutContrat, idCCUSouscripteur,  id)
a:  INDEX(idUser)
t:  INDEX(idUser,  dateMaj, numero, principal)
m:  INDEX(idUser,  dateMaj, adresse, principal)
ads:  INDEX(idUser,  ligne4, ligne6)
ts:  INDEX(idUser,  numero)
ms:  INDEX(idUser,  adresse)
pp:  INDEX(idClient,  idOrigine)
rc:  INDEX(idCCU,  numero)

It may be better to make this change; I do not know for sure:
Instead of, for example,

GROUP_CONCAT(DISTINCT ts.numero SEPARATOR ';') as numerous,
...
LEFT OUTER JOIN  ccu_telephone_v3bis ts  ON ts.idUser = u.id

get rid of the JOIN and change the select part to

( SELECT GROUP_CONCAT(DISTINCT ts.numero SEPARATOR ';') as numerous
       FROM ccu_telephone_v3bis ts  WHERE  ts.idUser = u.id
)  as numerous,

Doing that to all the aggregates should let you get rid of

GROUP BY u.id

This

case when ctp1.idContratDGP is null then false else true end

can be simplified to

ctp1.idContratDGP is NOT null

Looking at that list of JOINs, I suspect the schema is "over-normalized".

mysql How optimize query

1条答案

相关问题

热门标签

最新问答