Bonjour,

Il y a quelques temps j'avais fait un formulaire qui aboutissait à la requête suivante (elle fonctionnait sans problème) :

SELECT
-- Partie 1 : Informations qui seront affichées --
A.Id AS IDR,
GROUP_CONCAT(DISTINCT V.Id SEPARATOR ', ') AS Dossiers,
CONCAT( CONCAT( 'Du volume ', V1.Volumes, ', chapitre ', V1.Dossiers ), ' ', CONCAT( 'au volume ', V2.Volumes, ', chapitre ', V2.Dossiers ) ) AS Manga, A.Resume AS Resume,
GROUP_CONCAT(DISTINCT PP.Nom SEPARATOR ', ') AS PersosP,
GROUP_CONCAT(DISTINCT PV.Nom SEPARATOR ', ') AS PersosV,
GROUP_CONCAT(DISTINCT PC.Nom SEPARATOR ', ') AS PersosC,
GROUP_CONCAT(DISTINCT L.Nom SEPARATOR ', ') AS Lieux,
GROUP_CONCAT(DISTINCT Ty.Nom SEPARATOR ', ') AS Types,
GROUP_CONCAT(DISTINCT Th.Nom SEPARATOR ', ') AS Themes,
GROUP_CONCAT(DISTINCT E.Numero SEPARATOR ', ') AS Episodes
FROM DC_ResVol_Resume A
INNER JOIN DC_ResVol_Dossiers LAD
    ON (A.Id = LAD.Rid)
INNER JOIN DC_Volumes V
    ON LAD.Did = V.Id
INNER JOIN DC_V_ResId_ChapMinMax VRCMM
    ON (A.Id = VRCMM.Id)
INNER JOIN DC_Volumes V1
    ON VRCMM.chap_min = V1.id
INNER JOIN DC_Volumes V2
    ON VRCMM.chap_max = V2.id
LEFT JOIN DC_ResVol_PersosP LAPP
    ON A.Id = LAPP.Rid
LEFT JOIN DC_Persos PP
    ON LAPP.Pid = PP.Id
LEFT JOIN DC_ResVol_PersosC LAPC
    ON A.Id = LAPC.Rid
LEFT JOIN DC_Persos PC
    ON LAPC.Cid = PC.Id
LEFT JOIN DC_ResVol_PersosV LAPV
    ON A.Id = LAPV.Rid
LEFT JOIN DC_Persos PV
    ON LAPV.sVid = PV.Id
LEFT JOIN DC_ResVol_Lieux LAL
    ON (A.Id = LAL.Rid)
LEFT JOIN DC_Lieux L
    ON (LAL.Lid = L.Id)
LEFT JOIN DC_ResVol_Types LATY
    ON (A.Id = LATY.Rid)
LEFT JOIN DC_Types TY
    ON (LATY.Tyid = TY.Id)
LEFT JOIN DC_ResVol_Themes LATH
    ON (A.Id = LATH.Rid)
LEFT JOIN DC_Themes TH
    ON (LATH.Thid = TH.Id)
LEFT JOIN DC_ResVol_Episodes LAE
    ON (A.Id = LAE.Rid)
LEFT JOIN DC_Episodes E
    ON (LAE.Episode_id = E.Id)

-- Partie 2 : Jointures effectuées par requête PHP --   
INNER JOIN DC_ResVol_Dossiers LAD2
    ON A.Id = LAD2.Rid
INNER JOIN DC_Volumes V3
    ON LAD2.Did = V3.Id
INNER JOIN DC_ResVol_Types LATY2
    ON A.Id = LATY2.Rid
INNER JOIN DC_Types TY2
    ON LATY2.Tyid = TY2.Id
INNER JOIN DC_ResVol_Lieux LAL2
    ON A.Id = LAL2.Rid
INNER JOIN DC_Lieux L2
    ON LAL2.Lid = L2.Id
INNER JOIN DC_ResVol_PersosP rp2
    ON A.Id = rp2.Rid
INNER JOIN DC_Persos p2
    ON rp2.Pid = p2.Id
INNER JOIN DC_ResVol_PersosP rp1
    ON A.Id = rp1.Rid
INNER JOIN DC_Persos p1
    ON rp1.Pid = p1.Id

-- Partie 3 : Conditions ajoutées par requête PHP --    
WHERE (
    (V3.Id BETWEEN :volume_depart AND :volume_fin)
    AND (TY2.Id = :type)
    AND (L2.Id = :lieu)
    AND (p2.Id = :persos2)
    AND (p1.Id = :persos1)
    ) 
GROUP BY A.Id

   
Seulement, il y avait un problème : DC_Volumes (nom mal choisi) contenait la liste des dossiers et le volume auquel ils étaient rattachés soit :


J'ai voulu faire les choses bien et séparer les volumes des dossiers en deux tables puis les relier par une clé étrangère :


Par conséquent, j'ai adapté la vue mentionnée à la ligne 18.
En outre, j'ai modifié le nom des tables et certaines colonnes pour que ce soit plus compréhensible. Au final, j'arrive à cette requête :

SELECT
--  Partie 1 : Informations qui seront affichées --
    A.Id AS IDR,
    GROUP_CONCAT(DISTINCT D.Id SEPARATOR ', ') AS Dossiers,
    CONCAT( CONCAT( 'Du volume ', V1.Numero, ', chapitre ', D1.Chapitre ), ' ', CONCAT( 'au volume ', V2.Numero, ', chapitre ', D2.Chapitre ) ) AS Manga,
    A.Resume AS Resume,
    GROUP_CONCAT(DISTINCT PP.Nom SEPARATOR ', ') AS PersosP,
    GROUP_CONCAT(DISTINCT PV.Nom SEPARATOR ', ') AS PersosV,
    GROUP_CONCAT(DISTINCT PC.Nom SEPARATOR ', ') AS PersosC,
    GROUP_CONCAT(DISTINCT L.Nom SEPARATOR ', ') AS Lieux,
    GROUP_CONCAT(DISTINCT Ty.Nom SEPARATOR ', ') AS Types,
    GROUP_CONCAT(DISTINCT Th.Nom SEPARATOR ', ') AS Themes,
    GROUP_CONCAT(DISTINCT E.Numero SEPARATOR ', ') AS Episodes
FROM DC_Affaires A
INNER JOIN DC_Lien_Affaires_dossiers LAD
    ON (A.Id = LAD.Affaire_Id)
INNER JOIN DC_Dossiers D
    ON LAD.Dossier_Id = D.Id
INNER JOIN DC_V_ResId_ChapMinMax VRCMM
    ON (A.Id = VRCMM.Id)
INNER JOIN DC_Dossiers D1
    ON VRCMM.chap_min = D1.Id
INNER JOIN DC_Volumes V1
    ON D1.Volume_Id = V1.Id
INNER JOIN DC_Dossiers D2
    ON VRCMM.chap_max = D2.Id
INNER JOIN DC_Volumes V2
    ON D2.Volume_Id = V2.Id
LEFT JOIN DC_Lien_Affaires_Persos_Presents LAPP
    ON A.Id = LAPP.Affaire_Id
LEFT JOIN DC_Personnages PP
    ON LAPP.Personnage_Id = PP.Id
LEFT JOIN DC_Lien_Affaires_Persos_Cites LAPC
    ON A.Id = LAPC.Affaire_Id
LEFT JOIN DC_Personnages PC
    ON LAPC.Personnage_Id = PC.Id
LEFT JOIN DC_Lien_Affaires_Persos_Victimes LAPV
    ON A.Id = LAPV.Affaire_Id
LEFT JOIN DC_Personnages PV
    ON LAPV.Personnage_Id = PV.Id
LEFT JOIN DC_Lien_Affaires_Lieux LAL
    ON (A.Id = LAL.Affaire_Id)
LEFT JOIN DC_Lieux L
    ON (LAL.Lieu_Id = L.Id)
LEFT JOIN DC_Lien_Affaires_Themes LATH
    ON (A.Id = LATH.Affaire_Id)
LEFT JOIN DC_Themes TH
    ON (LATH.Theme_Id = TH.Id)
LEFT JOIN DC_Lien_Affaires_Types LATY
    ON (A.Id = LATY.Affaire_Id)
LEFT JOIN DC_Types TY
    ON (LATY.Type_Id = TY.Id)
LEFT JOIN DC_Lien_Affaires_Episodes LAE
    ON (A.Id = LAE.Affaire_Id)
LEFT JOIN DC_Episodes E
    ON (LAE.Episode_Id = E.Id)
   
-- Partie 2 : Jointures effectuées par requête PHP --
INNER JOIN DC_Lien_Affaires_dossiers LAD2
    ON A.Id = LAD2.Affaire_Id
INNER JOIN DC_Lien_Affaires_Types LATY2
    ON A.Id = LATY2.Affaire_Id
INNER JOIN DC_Types TY2
    ON LATY2.Type_Id = TY2.Id
INNER JOIN DC_Lien_Affaires_Lieux LAL2
    ON A.Id = LAL2.Affaire_Id
INNER JOIN DC_Lieux L2
    ON LAL2.Lieu_Id = L2.Id
INNER JOIN DC_Lien_Affaires_Persos_Presents rp2
    ON A.Id = rp2.Affaire_Id
INNER JOIN DC_Personnages p2
    ON rp2.Personnage_Id = p2.Id
INNER JOIN DC_Lien_Affaires_Persos_Presents rp1
    ON A.Id = rp1.Affaire_Id
INNER JOIN DC_Personnages p1
ON rp1.Personnage_Id = p1.Id

-- Partie 3 : Conditions ajoutées par requête PHP --
WHERE (
    (LAD2.Dossier_Id BETWEEN :dossier_depart AND :dossier_fin)
    AND (TY2.Id = :type)
    AND (L2.Id = :lieu)
    AND (p2.Id = :persos2)
    AND (p1.Id = :persos1)
    )
    GROUP BY A.Id

   
Cette requête fonctionne et arrive au même résultat mais est très lente (20 secondes) par rapport à la première (< 1 seconde). Je n'arrive pas à comprendre pourquoi. Pouvez-vous m'aider ? Comment puis-je l'optimiser ? Est-ce que créer une vue à partir de la première partie est recommandée (cela ferait une vue dans une autre) ?

Merci de votre aide,

EDIT : Je viens d'essayer en créant une vue sur la première partie. C'est plus rapide (4-5 secondes) mais toujours lent par rapport à la première requête.

J'ai essayé de supprimer les alias dans la partie 2, rien ne change non plus...

-
Edité par KerberosK 16 février 2020 à 18:37:29

Bonjour,

Pour creuser un problème de lenteur, il faut regarder du côté des index.

Je te conseille la lecture de ce site : https://use-the-index-luke.com/fr/

Il te faudra d'abord observer le plan d'exécution de la requête, avec la commande EXPLAIN.

Bonjour,

Merci pour la réponse.
Effectivement, c'était bien une piste à creuser. En retouchant les tables, j'avais également touché aux INDEX. J'ai comparé avec l'ancienne architecture et la principale différence résidait dans le fait que mes champs "Id" n'étaient plus des clefs primaires, seulement des INDEX. Donc, dans toutes mes tables principales, j'ai mis le champ "Id" en PRIMAIRE et là... on passe de plus de 25 secondes à 5 secondes.

Voici l'ancien EXPLAIN :


Et voici le nouveau :


J'ai du mal à les interpréter. J'ai l'impression que certains problèmes dans "EXTRA" ont disparu quand d'autres sont apparus.

J'ai du mal à saisir la différence entre clé primaire et Index. Si je comprends bien ce qui est indiqué sur ce site, la clé primaire permet de rendre identifiable chaque ligne d'une table (et refuse la valeur NULL). Et l'index sert à la recherche pour retrouver la valeur (la ligne) plus rapidement.

Donc est-ce qu'un champ "Id" dans une table peut-il être et clé primaire et index ? Est-ce pertinent si on recherche souvent par "Id" ? Par ailleurs, sur ton lien, il est dit que le système "B-Tree" est plus performant, donc il faudrait l'activer ?

J'ai regardé ces sites :

https://use-the-index-luke.com/fr/sql/plans-dexecution/mysql/operations#index-only-scan
https://blog.seboss666.info/2015/11/utiliser-explain-pour-ecrire-de-meilleures-requetes-sql/
http://sylvain.benest.free.fr/Documentation%20APACHE_PHP_MYSQL/Manuel%20MYSQL/Manuel%20html/explain.html

J'arrive vaguement à comprendre le tableau EXPLAIN, mais je ne parviens pas à comprendre si mes index sont bons ou non. Lorsque je regarde la colonne "possible_keys" je vois que cela correspond en grande partie à la colonne "keys".

Sur le quatrième lien, il est dit il est dit que les "const" et les "eq_ref" que j'ai  dans la colonne "type" sont les plus efficaces. Donc, peut-on faire mieux ? Puis il est dit :
"Si vous voulez rendre vos requêtes aussi rapide que possible, vous devriez examiner les lignes qui utilisent Using filesort et Using temporary."

Pour moi cela correspond à la 5e ligne avec la table RP2. Il s'agit d'une requête qui s'ajoute en fonction des cases à cocher du formulaire rempli par l'utilisateur. De fait je ne comprends pas pourquoi cette ligne est différente de RP1.

Bref je suis un peu dans le flou. j'ai gagné en rapidité en mettant mes "Id" en Primaire (sauf dans les tables de relation) et je ne sais pas si je dois me contenter de cela ou non. Sinon, je ne sais pas trop par où commencer. J'ai l'impression de toucher là à quelque chose de très technique.

KerberosK a écrit:

un champ "Id" dans une table peut-il être et clé primaire et index ?

Une clé primaire est un index, enfin, un type d'index.

KerberosK a écrit:

J'ai l'impression de toucher là à quelque chose de très technique.

C'est vrai, même les bons ne s'y frotte pas trop

KerberosK a écrit:

on passe de plus de 25 secondes à 5 secondes

C'est encore un peu long je trouve. Quelle est la volumétrie de tes principales tables ?

-
Edité par Benzouye 17 février 2020 à 19:03:17

En fait, le temps d'exécution, varie énormément. J'ai fait la même requête de nombreuses fois aujourd'hui et cela varie entre 1 et 5 secondes. Je ne sais pas trop comment expliquer la différence.

Mes tables sont plutôt légères. En rouge, les tables principales appelées dans la requête et en bleu les tables de relation (j'ai tronqué la colonne du milieu pour gagner de la place :

Je me demande (question naïve) si la lenteur ne viendrait pas de l'appel répété des mêmes tables comme DC_Personnages qui est appelé 5 fois avec des alias différents (PP, PC, PV, p1, p2).

Mais si même les bons ne s'y frottent pas trop, je pense que je vais éviter de faire n'importe quoi

Côté SQL tu peux regarder pour limitrt le nombre de LEFT JOIN pour ne laisser que ceux absolument nécessaires, en gros en LEFT JOIN seulement les tables pour lesquelles il est possible qu'aucun enregistrement n'existe.

Tu peux également remplacer le BETWEEN par un couple ... >= ...  AND ... <= ...

Bonjour,

Merci pour ta réponse et pour ton aide.

En remplaçant uniquement le BETWEEN par les opérateurs, je tourne autour de 1,9 seconde (testé directement sur la console mariadb).

Par contre pour remplacer les LEFT JOIn par des INNER JOIN, ça a produit tout l'effet inverse. Dans la première partie du code, j'ai voulu remplacer ces lignes :

LEFT JOIN DC_Lien_Affaires_Persos_Presents LAPP
    ON A.Id = LAPP.Affaire_Id
LEFT JOIN DC_Personnages PP
    ON LAPP.Personnage_Id = PP.Id 

par :

INNER JOIN DC_Lien_Affaires_Persos_Presents LAPP
    ON A.Id = LAPP.Affaire_Id
INNER JOIN DC_Personnages PP
    ON LAPP.Personnage_Id = PP.Id 

Considérant que dans une histoire, il y a forcément des personnages présents, cela semblait logique. Résultat : 2'16".

J'ai donc laissé le LEFT sur ces lignes et mis des INNER pour les lignes suivantes et j'ai remonté ces dernières dans le code :

INNER JOIN DC_Lien_Affaires_Lieux LAL
    ON (A.Id = LAL.Affaire_Id)
INNER JOIN DC_Lieux L
    ON (LAL.Lieu_Id = L.Id)
INNER JOIN DC_Lien_Affaires_Types LATY
    ON (A.Id = LATY.Affaire_Id)
INNER JOIN DC_Types TY
    ON (LATY.Type_Id = TY.Id)

   
Résultat : 33 secondes.

À chaque fois, EXPLAIN n'indique rien de plus que ce que j'ai déjà montré.

Du coup, j'ai tenté l'inverse, de remplacer les premiers INNER JOIN par des LEFT JOIN :

LEFT JOIN DC_Lien_Affaires_dossiers LAD 
	ON (A.Id = LAD.Affaire_Id) 
LEFT JOIN DC_Dossiers D 
	ON LAD.Dossier_Id = D.Id 
LEFT JOIN DC_V_ResId_ChapMinMax VRCMM 
	ON (A.Id = VRCMM.Id) 
LEFT JOIN DC_Dossiers D1 
	ON VRCMM.chap_min = D1.Id 
LEFT JOIN DC_Volumes V1 
	ON D1.Volume_Id = V1.Id 
LEFT JOIN DC_Dossiers D2 
	ON VRCMM.chap_max = D2.Id 
LEFT JOIN DC_Volumes V2 
	ON D2.Volume_Id = V2.Id 

et là j'arrive à moins de 1,5 secondes. Bizarre.

Je vais donc rester sur ma version actuelle (avec le BETWEEN remplacé) qui semble être le meilleur compromis entre logique et rapidité.

Merci pour ton aide