optimisation requête et jointures

Bonjour,

J'ai un problème avec une requête MySQL qui met de plus en plus de temps à s’exécuter, à mon avis à cause d'un select imbriqué, et ne trouve pas comment l'optimisé.

La requête tape les bases suivantes :
- claim => liste des sinistres
- action => liste des actions sur les sinistres
- user => liste des utilisateurs
- memberClient => liste les clients membre d'un groupe
- noteClient => liste les groupe clients disponible

Le but de la requête est de récupéré une liste des sinistres contenant la dernière action créé dessus (id le plus haut) puis de le trier par date d'action prévu ascendant.

La requête actuelle :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SELECT DISTINCT a.idClaim as sin, b.dateprevue as dateprevue, c.nom_user as nom, CASE WHEN d.codeMemberClient IS NOT NULL THEN e.color_noteClient ELSE 'FFFFFF' END as color FROM memberClient as d right join claim as a on a.clientCode = d.codeMemberClient left join noteClient as e on d.noteMemberClient = e.id_noteClient, action as b, user as c WHERE a.idClaim=b.nsinistrea AND b.dateprevue = ( SELECT b.dateprevue FROM action as b WHERE b.nsinistrea = a.idClaim ORDER BY actionid DESC LIMIT 1 ) AND b.utilisateur=c.id_user AND b.utilisateur IN ("liste des id user") GROUP BY b.nsinistrea ORDER BY b.dateprevue ASC, b.actionid DESC

---

Une idée ?

Merci beaucoup d'avance !

Bonjour popom31,

ll faudrait commencer par effectuer un EXPLAIN de la requête.

Bonjour fsmrel,

Merci pour le retour, ça me redonne un petit espoir !

La requête complète avec un explain et le résultat :

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

EXPLAIN SELECT DISTINCT a.idClaim as sin, a.clientName as nomclient, a.clientCode as codeclient, a.nContract as cont, a.brand as marque, a.model as modele, a.comLastname as comm, a.comName as com, a.persistence as persis, b.dateprevue as dateprevue, b.typeaction as typeaction, b.actionid as actionid, c.nom_user as nom, c.prenom_user as prenom, CASE WHEN d.codeMemberClient IS NOT NULL THEN e.color_noteClient ELSE 'FFFFFF' END as color FROM memberClient as d right join claim as a on a.clientCode = d.codeMemberClient left join noteClient as e on d.noteMemberClient = e.id_noteClient, action as b, user as c WHERE a.idClaim=b.nsinistrea AND b.dateprevue = (SELECT b.dateprevue FROM action as b WHERE b.nsinistrea = a.idClaim ORDER BY actionid DESC LIMIT 1) AND b.utilisateur=c.id_user AND (a.state = '-1' OR a.persistence = '1') AND b.utilisateur IN ('2','12','13','14','15','16','17','18','19','20','21','22','23','27') GROUP BY b.nsinistrea ORDER BY b.dateprevue ASC, b.actionid DESC

---

Pièce jointe 186171

Si je comprend le retour, la première étape boucle dans 26000 lignes et doit causer le problème ?

C'était mon idée de la cause, je ne trouve pas comment le contourner.

Merci

D’après la 1re ligne de l’explain, MySQL commence par attaquer la table ACTION (b), qu’il joint à la table USER (a) selon la condition b.utilisateur=c.id_user.

La colonne utilisateur de la table ACTION est-elle dotée d’un index ?

Plus généralement, quels sont les index des tables ACTION, USER, CLAIM ?

Merci.
Table ACTION : index "actionid"
Table USER : index "id_user"
Table CLAIM : index "idClaim"

Pourriez-vous :

1) créer un index pour la colonne utilisateur de la table ACTION, puis relancer l'explain ?

2) créer un index sur la colonne nsinistrea de la table ACTION, puis relancer l'explain ?

Je vais arrêter les merci à chaque post, juste un dernier pour le temps passé et l'aide !

Index sur la colonne utilisateur :

Pièce jointe 186238

Index sur la colonne nsinistrea:

Pièce jointe 186239

Ou sur les deux, je ne suis pas sur d'avoir bien tout compris :

Pièce jointe 186240

Bon... La pêche n’a pas été fructueuse, on va regarder du côté de la requête emboîtée :

Code:

---

1 2 3 4

AND b.dateprevue = (SELECT b.dateprevue FROM action as b WHERE b.nsinistrea = a.idClaim ORDER BY actionid DESC LIMIT 1)

---

Avant de torturer cette requête qui trie, que donne l’explain quand on crée un index sur la paire de colonnes (dateprevue, nsinistrea) de la table ACTION ? Le traitement est-il toujours aussi lent ?

Bonjour fsmrel,

Temp d'execution de la requête avant l'ajout des index : 83 résultats en 107.97s
Temp d'execution de la requête après l'ajout des index : 83 résultats en 111.48s

Et l'explain qui va avec :
Pièce jointe 186267

Je ne suis pas contre torturer cette requête imbriqué qui me fatigue depuis un moment !

Mouais... L'affaire se corse ! (chef-lieu Ajaccio)

Pour chacune des 27000 lignes du résultat, MySQL aura d’abord passé du temps à trier, du fait de ORDER BY actionid avant de retenir ces lignes.

Pouvez-vous voir ce que ça donne avec le scénario suivant, selon lequel on crée une table temporaire où, pour chaque claim, on va retrouver la plus grande valeur de actionid ?

CREATE TEMPORARY TABLE Temporaire
(
        idClaim     INTEGER NOT NULL
      , actionid    INTEGER NOT NULL
    , CONSTRAINT Temporaire_pk PRIMARY KEY (idclaim, actionid)
) ;
 
DELETE FROM temporaire ;

INSERT INTO temporaire 
     SELECT a.idClaim, MAX(b.actionid)
     FROM ACTION AS b, CLAIM AS a
     WHERE b.nsinistrea = a.idClaim
     GROUP BY a.idClaim
;

Puis :

EXPLAIN
SELECT a.idClaim, b.actionid, b.dateprevue
FROM   CLAIM AS a
     , ACTION AS b
     , temporaire AS t
WHERE  a.idClaim = b.nsinistrea
AND    a.idClaim = t.idClaim AND b.actionid = t.actionid   

Et, si cela n’est pas en contradiction avec les résultats attendus (j’ai pu mal interpréter le sens de la requête...), le coût de l’opération ?

Tout d'abord il ne faut pas mélanger les 2 syntaxes (ANSI et la virgule dans le FROM) c'est l'une ou l'autre.
Autant utiliser la syntaxe ANSI, de plus que pour les jointures externes il n'y a pas le choix avec mysql.

Ensuite, utiliser des alias c'est bien mais claim as a, action as b, user as c... :aie:
Il est préférable d'utiliser des alias qui parlent à la lecture de la requête. (Ok je pense que vous n'êtes pas l'auteur de la requête)

Enfin, je pense qu'il faut reprendre la requête corrélée car mysql n'est pas toujours très bon.
J'ai réécris la requête pour essayer d'avoir un ordre d'accès au table moins tordu, j'espère ne pas mettre planté, y compris dans la réécriture de la requête corrélée.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

select c.idClaim as sin, a.dateprevue as dateprevue, u.nom_user as nom, CASE WHEN mc.codeMemberClient IS NOT NULL THEN nc.color_noteClient ELSE 'FFFFFF' END as color from user u join action a on a.utilisateur = u.id_user join (select nsinistrea, max(actionid) as max_actionid from action group by nsinistrea ) temp on temp.nsinistrea = a.nsinistrea and temp.max_actionid = a.actionid join claim c on c.idClaim = a.nsinistrea left join memberClient mc on mc.codeMemberClient = c.clientCode left join noteClient nc on nc.id_noteClient = mc.noteMemberClient where u.id_user IN ("liste des id user") ORDER BY a.dateprevue ASC, a.actionid DESC

---

PS : j'ai fait sauter le DISTINCT, peut être à tord, mais surtout j'ai fait sauté le GROUP BY qui est mal employé.
Si vous avez des problèmes de résultats, exécutez votre précédente requête sans le GROUP BY.
Si les résultats changent sans le GROUP BY, c'est que la requête était fausse, et qu'il est nécessaire de reprendre fonctionnellement le besoin pour l'exprimer correctement.

A lire concernant le GROUP BY et mysql :
http://cedric-duprez.developpez.com/...r-group-by/#L3

Bonjour fsmrel, skuatamad,

La requête est parfaite !! Je vais me pencher dessus pour essayer de comprendre l'énorme différence de perf.

Un grand merci à vous deux pour votre aide !

Bonjour,

Citation:

---

Envoyé par skuatamad

Tout d'abord il ne faut pas mélanger les 2 syntaxes (ANSI et la virgule dans le FROM) c'est l'une ou l'autre.

---

Pourquoi donc ? La norme SQL accepte les deux syntaxes, et un optimiseur digne de ce nom est indifférent au style utilisé. Il est vrai qu’en général on se cantonne à un seul style, mais quoi qu’il en soit, l’objet de la discussion n’est pas là.

Citation:

---

Envoyé par skuatamad

Il est préférable d'utiliser des alias qui parlent à la lecture de la requête.

---

Là encore, chacun son style. Ainsi, a, b, c, etc. c’est dense et ça ne perturbe pas la lecture, sinon on pourrait faire la même observation concernant l’algèbre ou la logique des prédicats : dans « x T y », utiliser des noms qui parlent.

Une fois de plus, le problème n’est pas là, c’est la performance de la sous-requête (ou table dérivée) à quoi on s'intéresse.


Cela dit, vous avez pris les devants.

Il est évident que, dans la progression de la recherche de la requête performante, si on reprend la sélection en deux temps (cf. message #10), il faut synthétiser cela.

Rappel :

1er temps

CREATE TEMPORARY TABLE Temporaire
(
        idClaim     INTEGER NOT NULL
      , actionid    INTEGER NOT NULL
    , CONSTRAINT Temporaire_pk PRIMARY KEY (idClaim, actionid)
) ;
 
DELETE FROM temporaire ;

INSERT INTO temporaire 
     SELECT a.idClaim, MAX(b.actionid)
     FROM ACTION AS b, CLAIM AS a
     WHERE b.nsinistrea = a.idClaim
     GROUP BY a.idClaim
;

2e temps (requête R1)

SELECT a.idClaim, b.actionid, b.dateprevue
FROM   CLAIM AS a
     , ACTION AS b
     , temporaire AS t
WHERE  a.idClaim = b.nsinistrea
AND    a.idClaim = t.idClaim AND b.actionid = t.actionid   

L’étape suivante consiste évidemment à synthétiser, remplacer dans le SELECT de la requête R1 la partie temporaire AS t par le SELECT qui a servi pour l’insert du 1er temps ( INSERT INTO temporaire), d’où la requête finale :

Requête R2

SELECT a.idClaim, b.actionid, b.dateprevue
FROM   CLAIM AS a
     , ACTION AS b

     , (SELECT a.idClaim, MAX(b.actionid) as actionid
        FROM ACTION AS b, CLAIM AS a
        WHERE b.nsinistrea = a.idClaim
        GROUP BY a.idClaim) as t

WHERE  a.idClaim = b.nsinistrea
AND    a.idClaim = t.idClaim AND b.actionid = t.actionid 
;

Mais bon, vous l'avez fait avant que je poursuive.

Citation:

---

Envoyé par popom31

La requête est parfaite !! Je vais me pencher dessus pour essayer de comprendre l'énorme différence de perf.

---

Pour revenir sur le message #10, on comprend facilement que l’utilisation de la table temporaire (requête R1) est performante. Partant de là, son injection en tant que table dérivée t dans la requête R2 ne dégrade pas les performances, car elle est calculée une seule fois comme le montre l’explain :

Les deux 1res lignes correspondent au calcul de t, puis celle-ci (reprise sous le nom de <derived2> est jointe à la table b (ACTION) puis à la table a (CLAIM) :

http://www.fsmwarden.com/developpez_...31_explain.png


Si vous rencontrez plus tard de nouveaux problèmes de performance, je vous engage à lancer des campagnes d'explains.


Never complain, always explain...

Citation:

---

Envoyé par fsmrel

Pourquoi donc ? La norme SQL accepte les deux syntaxes, et un optimiseur digne de ce nom est indifférent au style utilisé.

---

Je n'ai rien contre l'utilisation de l'une ou de l'autre, mais le mélange au sein d'une même requête complexifie grandement la lecture.
Comme mysql n'a pas de syntaxe à base de (+) ou de * pour les jointures externes, du coup seule la synatxe ANSI est disponible.

A titre personnel je suis plus habitué à la syntaxe ANSI, mais ma critique portait surtout sur le mélange des syntaxes dans une même requête, syntaxiquement possible mais illisible...

Citation:

---

Envoyé par fsmrel

Là encore, chacun son style. Ainsi, a, b, c, etc. c’est dense et ça ne perturbe pas la lecture

---

Je n'ai rien contre l'utilisation d'alias comme a, b ou c, c'est d'ailleurs ce que j'ai utilisé dans la requête.

Mais :
- a pour claim
- b pour action
- c pour user

Ca me semble anormalement piégeux ! :aie:

J'ai bien compris comment les alias étaient affectés, normal si la requête est générée automatiquement par un outil, mais pas vraiment pour un développeur qui devra maintenir la requête.

J'ai donc modifié pour une meilleure lisibilité en :
- c pour claim
- a pour action
- u pour user

J'utilise généralement la 1ere lettre de la table ou alors les 1eres lettres dans le cadre d'un nom de table composé, comme memberClient as mc

Citation:

---

Envoyé par fsmrel

Cela dit, vous avez pris les devants.

---

J'avais seulement survolé votre réponse et je n'avais pas vu l'intérêt de la table temporaire, du coup j'ai réécrit la requête.
Après avoir posté, je me suis rendu compte que ma table dérivée était votre table temporaire, ce qui m'a conforté dans la bonne réécriture de la requête globale :)

Citation:

---

Envoyé par fsmrel

Never complain, always explain...

---

Amen :D

Citation:

---

Envoyé par fsmrel

Mouais... L'affaire se corse ! (chef-lieu Ajaccio)

---

Puis-je te rappeler que dans Hellzapoppin, c'est "capitale Ajaccio" et non chef lieu !

Quel manque de culture... pfouf !!! :mouarf:

A +

Hum... Ça fait plus de 50 ans que je n’ai pas vu Hellzapoppin, et j’ai donc un peu oublié les sous-titres de Pierre Dac... Mais je note que Jacques Pessis, le meilleur spécialiste du maître 63 parle bien de « chef-lieu Ajaccio »...

Bon, maintenant si tu as le DVD, peux-tu me le prêter ?

Citation:

---

Envoyé par fsmrel

Hum... Ça fait plus de 50 ans que je n’ai pas vu Hellzapoppin, et j’ai donc un peu oublié les sous-titres de Pierre Dac... Mais je note que Jacques Pessis, le meilleur spécialiste du maître 63 parle bien de « chef-lieu Ajaccio »...

Bon, maintenant si tu as le DVD, peux-tu me le prêter ?

---

Hélas c'était en VHS et les bandes ont pourries dans mon garage !

Sinon : http://www.amazon.fr/Hellzapoppin-Ol...ds=helzappopin

A +