Discussion :

[Xenofexs]

Salut

Je dois optimiser une requête qui actuellement s’exécute en .... 19secondes malgré qu'elle soit très simple.

J'ai du coup fait ma petite requête qui s'exécute en 0.4s ce qui est nettement plus raisonnable seulement voila, je n'ai pas le même nombre de résultats

Je voudrai savoir si quelqu'un pouvait m'expliquer les réelles différences entre ces deux requêtes. Aussi est ce que la mienne peut suffire malgré le moindre retour (l'ancienne fait elle des doublons?)

Bref, place au code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT * 
FROM cc_inscription 
LEFT JOIN cc_paiement 
ON cc_paiement.inscription_id = cc_inscription.inscription_id 
WHERE cc_inscription.course_id=1 
ORDER BY cc_inscription.inscription_id ASC; 
(4107 records, 19s execution)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT * 
FROM cc_inscription, cc_paiement 
WHERE cc_paiement.inscription_id = cc_inscription.inscription_id 
AND cc_inscription.course_id=1 
ORDER BY cc_inscription.inscription_id ASC; 
(3780 records, 0.400s)

Si les résultats sont effectivement différents (donc que le LEFT JOIN ne provoque pas de doublons).

Pourriez vous m'indiquer comment je pourrai l'optimiser histoire qu'elle ne fasse pas 19secondes ?

Merci beaucoup !

[aieeeuuuuu]

rien à voir avec les doublons : la jointure externe renvoie en plus les lignes de la table cc_inscription pour lesquelles il n'existe pas de ligne dans la table cc_paiement qui satisfasse la condition de jointure.

une telle différence de temps d’exécution est tout de même étrange. Ne manque-t-il pas un index ? il faudrait comparer les deux plans d’exécution.

[Xenofexs]

Je doute fort qu'il y ait un quelconque index en effet ... et j'avoue avoir vu ça viteuf en cours sans vraiment savoir ce que c'est ni comment en faire un mais je suis tout à fait preneur si on peut m'expliquer

Merci pour ta réponse en tout cas

[Xenofexs]

Je re-post du coup désolé j'ai du mal m'exprimer. Pourriez vous m'expliquer comment je pourrai optimiser cette requête avec un système d'index ?

En ce qui concerne les 'plans d'exécutions' je ne vois pas trop ce dont tu parle. La manière dont sont faites les requêtes ? C'est sous navicat que j'ai ces résultats donc sans passer par un autre langage (de base le script est php mais les temps d’exécutions sont ceux de navicat directement)

[StringBuilder]

Le problème de performances me semble plutôt venir du cache... Après la première requête, les données sont déjà en cache (la seconde est un sous-ensemble de la première).

Donc la première, lectures physiques sur le disque, seconde, traitements uniquement en mémoire.

J'aimerais bien voir ce que ça donne si on les lance dans l'ordre inverse.

Car un LEFT OUTER JOIN n'est pas plus lent qu'un CROSS JOIN combiné à un filtre, on n'est plus à l'époque de Ingres...

[aieeeuuuuu]

combien y a-t-il de lignes dans chaque table ?

[fsmrel]

La remarque d’aieeeuuuuu relative aux plans d’exécution est importante. C’est dans un plan d’exécution que le SGBD rend compte de la façon dont il a procédé pour accéder aux données à partir d'une requête :

— Utilisation ou non des index ;

— Pour chaque index utilisé, la technique employée (boucles imbriquées, appareillage, etc.)

D’une manière générale, pour avoir ces informations, on soumet une instruction EXPLAIN PLAN, mais, navicat ou pas navicat, la façon de le faire dépend du SGBD, toutefois cette instruction n’est pas normalisée. Question préalable donc : quel est votre SGBD ? MySQL ? PostgreSQL ? Autre ?

[Xenofexs]

On travail sur MySQL

Pour ce qui est de l'ordre dans lesquelles sont effectuées les requêtes, je comprends pourquoi ça changerait quelque chose lors de l'exécution du php, mais pas pourquoi ça changerait via navicat (se sont dans ce dernier cas 2 requêtes effectuées séparément et il n'y a pas de cache ou autre sur navicat)

Il y a 7654 enregistrement dans la table paiement
7804 inscription

La base en elle même totalise un petit 40 Mo tout de même (pas que ces 2 tables of course)

Merci de vous pencher sur mon problème en tout cas

[aieeeuuuuu]

Bonjour,

Lorsque vous faites un UNION [ALL] de deux requêtes, cela ne fait plus qu'une requête. Et le moteur a toute la liberté de la traiter comme cela lui semble optimal. Ici visiblement il se méprend largement, certainement à cause de cardinalités mal évaluées. Ceci est en général dû des des statistiques pas à jour (ou pas de statistiques du tout!).

Est-ce que votre contrainte référentielle entre ces deux tables est déclarée ?

ps : vous n'avez pas indiqué le nombre de lignes dans les tables.

[Xenofexs]

Bon, voici tout ce que je peux vous envoyer

Table inscription 7804 enregistrements



Table paiement, 7654 enregistrements



Je sais pas trop de quoi tu parle avec les statistiques. Ni avec une contrainte référentielle

[fsmrel]

Pour en revenir à vos deux requêtes initiales, merci de présenter les résultats de l'exécution de l’instruction EXPLAIN :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
EXPLAIN
SELECT * 
FROM cc_inscription 
LEFT JOIN cc_paiement 
ON cc_paiement.inscription_id = cc_inscription.inscription_id 
WHERE cc_inscription.course_id=1 
ORDER BY cc_inscription.inscription_id ASC;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
EXPLAIN
SELECT * 
FROM cc_inscription, cc_paiement 
WHERE cc_paiement.inscription_id = cc_inscription.inscription_id 
AND cc_inscription.course_id=1 
ORDER BY cc_inscription.inscription_id ASC;

Et tant qu’à faire :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
EXPLAIN
SELECT * 
FROM cc_inscription 
INNER JOIN cc_paiement 
ON cc_paiement.inscription_id = cc_inscription.inscription_id 
WHERE cc_inscription.course_id=1 
ORDER BY cc_inscription.inscription_id ASC;

Variantes :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
EXPLAIN
SELECT * 
FROM cc_inscription 
LEFT JOIN cc_paiement 
ON cc_paiement.inscription_id = cc_inscription.inscription_id 
AND  cc_inscription.course_id=1 
ORDER BY cc_inscription.inscription_id ASC;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
EXPLAIN
SELECT * 
FROM cc_inscription 
INNER JOIN cc_paiement 
ON cc_paiement.inscription_id = cc_inscription.inscription_id 
AND  cc_inscription.course_id=1 
ORDER BY cc_inscription.inscription_id ASC;

[fsmrel]

Je sais pas trop de quoi tu parles avec les statistiques.

Un SGBD relationnel a la particularité d’être équipé d’un optimiseur sémantique, dont le rôle est de décider de la façon de naviguer dans les tables, sous le capot. L’optimiseur programme les méthodes d’accès en s’appuyant sur le catalogue relationnel (la métabase pour faire bien), qui contient non seulement la description de la structure de l’ensemble des tables, mais aussi de plein d’autres informations, dont les index (qui sont des arbres équilibrés, autrement dit des turbos), la volumétrie des tables, etc.

Si l’optimiseur constate que la table T contient des dizaines de millions de lignes, et si l’on a codé SELECT ... FROM T WHERE C1 = 12, il va vérifier si un index est présent pour la colonne C1. Si oui, en deux ou trois accès la requête sera satisfaite, la réponse sera instantanée. Sinon l’optimiseur programmera un balayage complet de la table, et vous aurez le temps d’aller boire votre café.

Index et Statistiques sont les deux mamelles auxquelles s’abreuve l’optimiseur. EXPLAIN nous permet de savoir objectivement ce qui se passera sous le capot.

[Xenofexs]

Ok merci

Les résultats, dans l'ordre.

J'avoue pas trop comprendre en quoi ces résultats peuvent vous aider mais bon...

[fsmrel]

Bonsoir,

Je ne sais pas trop de quoi tu parles avec les statistiques.

Si vous consultez la doc MySQL, vous pourrez décortiquer la partie EXPLAIN...

1) MySQL commence par dire ceci :

EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement.

Ainsi, dans les cas 1 et 4 (présence du LEFT JOIN), MySQL a commencé par lire la table INSCRIPTION, tandis que dans les autres cas, il a commencé par lire la table PAIEMENT.

2) La colonne Type permet de savoir comment est traitée la jointure. Quand cette colonne prend la valeur « ALL », voici ce que dit MySQL :

ALL A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

Un full table scan est un balayage complet de la table concerné, ce qui n’est pas bon....

Ainsi, quand pour la table INSCRIPTION et pour la table PAIEMENT la colonne Type prend la valeur « ALL », cela veut dire que MySQL balaiera complètement la table INSCRIPTION, puis pour chaque ligne lue, il balaiera l’ensemble des lignes de la table PAIEMENT: on a un splendide produit cartésien, dont MySQL extraira les lignes à retenir.

Si donc INSCRIPTION comporte 7731 lignes et PAIEMENT 7230 lignes, le nombre total de lectures est égal à environ 56^10.6, c’est beaucoup, et ça justifie les 20 secondes...

Quand pour la table INSCRIPTION, la colonne prend la valeur « eq_ref », cela veut dire que pour chaque ligne lue de la table PAIEMENT, MySQL ne lira qu’une ligne de la table INSCRIPTION. Si donc INSCRIPTION comporte 7731lignes et PAIEMENT 7230 lignes, le nombre total de lectures est égal à 7230 +7 331 (le produit a été transformé en somme), c'est-à-dire approximativement 4000 fois moins que dans le cas précédent, mais ça reste fortement améliorable.


3) La colonne possible_keys permet de savoir quel index est utilisé pour l’accès à la table concernée. Dans vos exemples, quand la colonne Type prend la valeur eq_ref, MySQL a utilisé l’index de la clé primaire de la table (INSCRIPTION en l’occurrence).

Mais en ce qui concerne la table PAIEMENT, manifestement, soit elle dépourvue d’index sur la colonne inscription_id, soit il y en a un, mais MySQL n’a pas voulu s’en servir, pour une raison qui reste à trouver : par exemple l’index n’est pas assez filtrant, c'est-à-dire que le nombre de lignes pour lesquelles inscription_id = 1 est très élevé, faisant que MySQL a toutes les chances de devoir aller faire un tour dans l’ensemble des pages affectées à la table.

Pour que l’on connaisse objectivement la situation des index, merci d’exécuter les commandes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SHOW INDEX FROM cc_inscription

Et surtout :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SHOW INDEX FROM cc_paiement

4) La colonne ref fournit le nom de la table et de la colonne dont MySQL va extraire les valeurs pour accéder à l'autre table qu’i va traiter. Dans votre cas, il s’agit de la colonne inscription_id de la table PAIEMENT, qui sera rapprochée de la colonne inscription_id de la table INSCRIPTION.

Etc.


=>

De tout cela, il ressort clairement, que la colonne inscription_id de la table PAIEMENT devrait être dotée d’un index, tout en sachant que, si cet index existe déjà, MySQL peut le disqualifier, par exemple parce qu’il n’est pas assez filtrant.


Question : combien de lignes de la table PAIEMENT vérifient la condition WHERE inscription_id = 1 ?


Quand on aura tout bien épluché, on se penchera s’il le faut sur le cas du LEFT JOIN qui empêche peut-être l’utilisation des index. Si fonctionnellement la jointure gauche se justifie, il existe des solutions alternatives pour la mettre en oeuvre, du genre de celles auxquelles on avait recours quand cet opérateur n’existait pas encore.


Quand aieeeuuuuu écrit :

Est-ce que votre contrainte référentielle entre ces deux tables est déclarée ?

Il a bigrement raison. En effet, sans clé étrangère permettant de garantir cette contrainte, vous pouvez vous retrouver avec des paiements sans inscription...

En outre, pourquoi la colonne inscription_id de la table PAIEMENT peut-elle être marquée NULL ? (Remarque valable pour les autres colonnes de vos tables...)

[Xenofexs]

Merci de nouveau pour votre réponse.

Voici pour les 2 requêtes :

Question : combien de lignes de la table PAIEMENT vérifient la condition WHERE inscription_id = 1 ?

S'il s'agit de savoir combien il y a de correspondance entre paiement et inscription :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Select * from cc_paiement, cc_inscription 
WHERE cc_paiement.inscription_id = cc_inscription.inscription_id
7104 réponses

S'il s'agit de savoir combien répondent à la condition =1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Select * from cc_paiement
WHERE inscription_id = 1

1 réponse

Est-ce que votre contrainte référentielle entre ces deux tables est déclarée ?

La question c'est de savoir si, dans paiement, inscription_id est déclaré en PRIMARY KEY ? La réponse est non. Par contre je l'ai rajouté à l'instant, et on gagne 2-3secondes sur la requête. C'est peut être d'ailleurs que du hasard (notre serveur a une vitesse un peu aléatoire. Je laisse paiement_id ET inscription_id en PRIMARY KEY du coup ou j'enlève inscription_id ?

Pour ce qui est d'avoir des NULL autorisé, aucune idée, ce n'est pas moi qui ai fait la table. J'ai uniquement des connaissances de scolaire de base et mes prédécesseurs n'en avait que des empiriques donc c'est normal que rien ne soit optimisé. C'est d'ailleurs pour ça que je me renseigne ici plutot que de bidouiller quelques trucs de mon coté

Merci encore en tout cas

[fsmrel]

Bonsoir Xenofexs,



Au vu du show index, il faut impérativement ajouter les index manquants :

— un index sur la colonne CourseId de la table INSCRIPTION puisque votre requête SQL comporte la clause WHERE cc_inscription.course_id = 1 : en l’absence de cet index, on ne peut que se traîner lamentablement, puisque MySQL est obligé de lire le contenu de chacune des 7730 lignes de la table, pour ne retenir que celles pour lesquelles course_id vaut 1 (à ce propos, combien y a-t-il de lignes de la table INSCRIPTION pour lesquelles on vérifie CourseId = 1 ?)

— un index sur la colonne inscription_id à cause de la jointure, car là encore il s’agit que MySQL ne lise pas l’intégralité de la table PAIEMENT, mais accède directement au paiement relatif à une inscription).

Pour que l’opération soit performante, il faut arriver à un EXPLAIN qui donne ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
Id  select-type  table        type   possible keys           key                     
--  -----------  -----------  ----   --------------------    ----------------------
1   SIMPLE       INSCRIPTION  ref    PRIMARY,                INSCRIPTION_CourseId
                                     INSCRIPTION_CourseId 

1   SIMPLE       PAIEMENT     ref    PAIEMENT_InscriptionId  PAIEMENT_InscriptionId  INSCRIPTION.InscriptionId

EXPLAIN qui montre que MySQL commencera par filtrer les inscriptions vérifiant la clauseWHERE cc_inscription.course_id = 1, ce qui devrait déblayer le terrain, puis ensuite, grâce à l’index sur la colonne InscriptionId, ne lire dans la table PAIEMENT que les lignes pour lesquelles la jointure est vérifiée.

=> Que donne l’EXPLAIN après ajout des index ?

A noter que si une inscription est faite pour une course donnée, la colonne CourseId doit être déclarée NOT NULL à l’occasion de la création de la table INSCRIPTION, sinon ça ne veut rien dire. Même chose concernant la colonne InscriptionId la table PAIEMENT.


Votre show index (un peu incomplet côté index_type) donne d’autres informations quant aux index :



Ainsi, on y lit que la table PAIEMENT contient 7231 lignes à la date du 11 août.

Tandis que selon la jointure :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT * FROM cc_paiement, cc_inscription 
WHERE cc_paiement.inscription_id = cc_inscription.inscription_id

Il n’y a que 7104 paiements correspondant à des inscriptions. Donc, de deux choses l’une :

— Ou bien il y a 127 généreuses personnes qui ont payé sans s’être inscrites ;

— Ou bien les inscriptions correspondant à ces 127 paiements ont été perdues, ce qui est quand même embêtant. Si chaque paiement correspond à une inscription, alors l’intégrité référentielle doit être mise en œuvre à tout prix :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE cc_paiement ADD CONSTRAINT FOREIGN KEY cc_paiement_FK (inscription_id) REFERENCES cc_inscription (inscription_id) ;

Mais il faudra d’abord évacuer (et mettre prudemment de côté) les paiements orphelins pour voir à quoi ils correspondent...

La question c'est de savoir si, dans paiement, inscription_id est déclaré en PRIMARY KEY ? La réponse est non. Par contre je l'ai rajouté à l'instant.

Hum... Une table ne peut comporter qu’une seule clé primaire. Si donc {paiement_id} est déjà clé primaire, {inscription_id} n’a rien à faire dans cette histoire (mais est clé étrangère comme on vient de le voir).


A propos de SELECT * :


Il faut éviter de coder « * », il est préférable de citer les noms de seules colonnes dont on a besoin dans le résultat. Ça peut en plus avoir un effet bénéfique sur la performance.

[fsmrel]

Bonjour Xenofexs,

Combien y a-t-il de lignes de la table INSCRIPTION pour lesquelles on vérifie CourseId = 1 ?

Je repose la question.

Que donne l’EXPLAIN après ajout des index ?

Qu’en est-il ?

Ou bien il y a 127 généreuses personnes qui ont payé sans s’être inscrites ;
Ou bien les inscriptions correspondant à ces 127 paiements ont été perdues.

Qu’en est-il ?

[Xenofexs]

Bonjour

Encore merci pour votre aide, je ne le répèterai jamais assez. Pour faire les tests "en conditions réelles" j'ai rapatrié la base de prod en préprod les chiffres ne seront peut être plus les mêmes ce qui expliquerait le problème au niveau inscription/paiement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT COUNT(inscription_id) FROM cc_inscription WHERE course_id = 1
= 4107

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
EXPLAIN
SELECT * 
FROM cc_inscription 
LEFT JOIN cc_paiement 
ON cc_paiement.inscription_id = cc_inscription.inscription_id 
WHERE cc_inscription.course_id=1 
ORDER BY cc_inscription.inscription_id ASC;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																						
1	SIMPLE	cc_inscription	ref	IDX_INSCRIPTION_COURSE_ID	IDX_INSCRIPTION_COURSE_ID	5	const	3426	Using where; Using temporary; Using filesort																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																						
1	SIMPLE	cc_paiement	ALL					169

Désolé de pas répondre très vite je suis en vacances jusqu'à mercredi. Encore une fois merci de m'aider. Dans tous les cas, l'ajout des index à eu l'air miraculeux et booster les requêtes (0.134s au dernier test)
D'autres points posent problème, mais je pense déjà pouvoir dire que ce sujet est résolu (j'attends une éventuelle réponse avant de le faire, ne sachant pas si cela cloture le thread ou pas)

[fsmrel]

Bonsoir Xenofexs,


Puisque vous êtes en vacances, on peut attendre mercredi avant de reprendre cette discussion (ne la marquez pas résolue tant qu’on n’en a pas fini ). L’index IDX_INSCRIPTION_COURSE_ID a fait beaucoup de bien, mais je m’étonne que, dans l’explain, la colonne Type ait la valeur « ALL » : avez-vous bien créé un index sur la colonne InscriptionId de la table cc_paiement ?

A votre retour, pourriez-vous voir ce que donne la requête (explain et temps d’exécution) de la requête suivante, équivalente à celle qui utilise LEFT JOIN :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT INSCRIPTION.* 
FROM   INSCRIPTION INNER JOIN PAIEMENT ON INSCRIPTION.InscriptionId = PAIEMENT.InscriptionId 
WHERE  CourseId = 1 
UNION ALL
SELECT INSCRIPTION.*
FROM   INSCRIPTION
WHERE  CourseId = 1
  AND NOT EXISTS (SELECT ' '
                  FROM   PAIEMENT
                  WHERE  INSCRIPTION.InscriptionId = PAIEMENT.InscriptionId) ;

[Xenofexs]

Bonjour,

Effectivement il manquait l'index sur PAIEMENT sur cette base (il y a une base en préprod qui ne peut être équivalente de la prod, donc j'ai du créer une 2eme en préprod qui est un clone de la prod... fin bref)

Donc pour l'explain précédent, les 2 types sont bien à ref

Pour celui ci maintenant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
EXPLAIN SELECT INSCRIPTION.* 
FROM   INSCRIPTION INNER JOIN PAIEMENT ON INSCRIPTION.InscriptionId = PAIEMENT.InscriptionId 
WHERE  CourseId = 1 
UNION ALL
SELECT INSCRIPTION.*
FROM   INSCRIPTION
WHERE  CourseId = 1
  AND NOT EXISTS (SELECT ' '
                  FROM   PAIEMENT
                  WHERE  INSCRIPTION.InscriptionId = PAIEMENT.InscriptionId) ;

Temps d’exécution sans l'explain : 0.24s

Je viens de voir cette requête qui est un peu longuette aussi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT * FROM cc_inscription 
 
LEFT JOIN cc_course ON cc_course.course_id = cc_inscription.course_id 
 
LEFT JOIN cc_statut_coureur ON cc_statut_coureur.statut_coureur_id = cc_inscription.statut_coureur_id 
	WHERE cc_course.course_relais != '1' 
 
	ORDER BY inscription_mail_statut,cc_inscription.inscription_date_rec DESC LIMIT 0, 100

Pour ça il faudrait un index sur
cc_course course_id
cc_statut_coureur.statut_coureur_id
cc_inscription.statut_coureur_id

Ou d'autres, ou pas tous ?


EDIT : Quelles seraient les conséquences si je mettais des indexs partout ? Sur tous les champs de toutes les bases ? Ca surchargerait la base, ça finirait pas la ralentir ?