Discussion :

[Mi895]

Bonjour,

Je souhaite optimiser la requête suivante (temps supérieur à 10sec) :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT ................................
FROM dpr_dossier_production AS d 
INNER JOIN dpr_produit AS p ON p.rid_dossierproductionid = d.dossierproductionid 
INNER JOIN dpr_prestation AS presta ON presta.rid_produitid = p.produitid 
INNER JOIN dpr_prestation_tsp AS tsp ON tsp.rid_prestationid = presta.prestationid 
INNER JOIN dpr_passager_prestation_tsp pp on pp.rid_prestationtspid=tsp.prestationtspid 
INNER JOIN dpr_passager pax on pax.passagerid=pp.rid_passagerid 
WHERE d.dossierproductionnumero = 'LLL' 
order by datedebut

J'ai bien des index sur chaque colonne utilisée pour une jointure, j'ai l'impression que c'est la ligne avec PAX qui pose soucis (voir plan d'exécution), quand je la retire le temps est divisé par 2.

Avez-vous une idée d'optimisation ?

Merci.

[CinePhil]

Avez vous essayé de changer l'ordre des tables en mettant dpr_passager pax en premier ?

Avez-vous vraiment besoin d'infos sur le passager dans la partie SELECT ?

Si vous nous décrivez plus précisément votre besoin, ce sera peut-être plus facile de vous aider.

Sinon, avec plus 1 millions de lignes, si le serveur n'est pas très performant, MySQL commence à sérieusement ralentir !

[Mi895]

Quand j'essaie de passer pax (qui correspond à dpr_passager) en 1er j'obtiens le message d'erreur suivant :

Code :

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

Lookup Error - MySQL Database Error: Unknown column 'dpr_passager_prestation_tsp.rid_passagerid' in 'on clause'

Sinon oui j'ai bien besoin des infos passagers dans mon SELECT.

Je crois que le ORDER BY allonge bien le temps d'exécution (d'après le EXPLAIN PLAN on a un filesort).

Merci de ton aide.

[CinePhil]

Je proposais de faire ça :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT ................................
FROM dpr_passager pax
INNER JOIN dpr_passager_prestation_tsp pp ON pax.passagerid=pp.rid_passagerid
	INNER JOIN dpr_prestation_tsp tsp ON pp.rid_prestationtspid=tsp.prestationtspid
		INNER JOIN dpr_prestation AS presta ON tsp.rid_prestationid = presta.prestationid
			INNER JOIN dpr_produit p ON presta.rid_produitid = p.produitid
				INNER JOIN dpr_dossier_production d ON p.rid_dossierproductionid = d.dossierproductionid
WHERE d.dossierproductionnumero = 'LLL' 
order by datedebut

Sinon, effectivement, le ORDER BY peut être pénalisant.
Si le nombre de lignes retourné par la requête est faible, il pourrait être plus rapide de faire le tri dans le programme applicatif.

[Mi895]

J'ai tenté t'as requête le temps est rallongé, je ne vois donc pas comment faire

[escartefigue]

Bonjour,

Quelques remarques :
- l'ordre dans lequel sont écrites les jointures n'a aucune importance d'un point de vue performances, ca peut éventuellement faciliter la lecture de la requête.
- vous avez une clef dont la longueur est de 248 ! c'est considérable ! Si votre CPU est une 64 bits, soit 8 octet, il lui faut 31 cycles CPU pour manipuler cette clef
- un order by allonge quasiment toujours le temps d'exécution, même si vous avez un index cluster et que celui ci est éligible. Et pour cause, le cluster ratio est rarement de 100% (sauf juste après une réorg)

[Mi895]

Merci pour la réponse, ca ne sert à rien de continuer à essayer d'inverser l'ordre des jointures donc ...!

Concernant la clef, effectivement elle est énorme surtout que les données sont du CHAR(15) au maximum apparemment ! Ca a quand même un impact ? Il ne dois pas considérer que les 15 caractères ?

Comment je peux éviter de parcourir les 1M de lignes de la table des passagers ? Dé-normaliser est la seule solution ?

Merci de votre aide!

[CinePhil]

Quel est votre besoin qui justifie cette requête ?

[Mi895]

c'est une requête pour le métier !

on me demande de l'optimiser ...

[CinePhil]

Je viens de voir un truc :
Dans le résultat de l'explain, le type de jointure pour pax est ALL !
Avez-vous un index sur la colonne de jointure de cette table ?

Je ne vois pas non plus de possible_keys sur pp.rid_passagerid. C'est possible si c'est la première colonne de la clé primaire de cette table qui semble être une table associative mais sinon, il manque peut-être un index sur cette colonne.

[escartefigue]

Merci pour la réponse, ca ne sert à rien de continuer à essayer d'inverser l'ordre des jointures donc ...!

En effet, mais attention, ma remarque ne s'applique que pour les jointures INNER (ou FULL OUTER), ce qui est votre cas présent, à ne pas extrapoler en cas de jointure LEFT ou RIGHT OUTER

Concernant la clef, effectivement elle est énorme surtout que les données sont du CHAR(15) au maximum apparemment ! Ca a quand même un impact ? Il ne dois pas considérer que les 15 caractères ?

Et bien non, si la clef fait potentiellement 248 , il est normal que le moteur de la base de données utilise la longueur maxi !.
La chose à ne pas faire : remplacer la colonne char(248) par du varchar(248) ce serait encore pire !
Avant tout vérifiez si la colonne de 248 car fait l'objet de contraintes de type REFERENCE dans d'autres tables, si c'est le cas, l'optimisation est assez galère car vous impactez toutes les tables ayant ces contraintes
Idéalement, créez une nouvelle colonne clef dans votre table, de type attribué par le SGBD (auto-incrément), la clef actuelle deviendra un simple attribut, non clef, ou clef candidate éventuellement unique, mais non PK.
Si vous avez pu vérifier qu'il n'existe jamais de longueur > 15 et aussi et surtout, qu'il n'y en aura jamais à l'avenir, profitez en pour raccourcir la colonne, même si elle n'est plus clef, les octets ont un coût (espace disque, charge réseau)

Comment je peux éviter de parcourir les 1M de lignes de la table des passagers ? Dé-normaliser est la seule solution ?

Si l'optimiseur choisit de parcourir séquentiellement la table, c'est qu'aucun index n'est pertinent (lapalisse n'aurait pas dit mieux )
Il y a de nombreuses raisons possibles, la plus simple étant l'absence d'index , ou plus probablement des colonnes de jointure de type et/ou de longueur différentes entre les deux tables, à vérifier.
Ou encore un index déclaré mais non discriminant. Cas typique un index sur le code sexe qui ne peut prendre que 2 voire 3 valeurs n'a aucun intérêt d'un point de vue critère de recherche.
Ou encore, la colonne est bien présente dans un index, mais avec d'autres colonnes qui la précèdent et que vous n'avez pas mentionné dans la jointure.

Communiquez la description de chacun des index des tables utilisées, ce sera plus concret.

[Mi895]

Concernant la table dpr_passager, j'ai bien une PK sur la colonne "passagerid".


Pour la table alias "pp" (là par contre j'ai 2 colonnes dans la PK ...)


J'ai aussi vérifié pour la jointure les colonnes sont du même type INT(11).

Code :

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

INNER JOIN dpr_passager pax on pax.passagerid=pp.rid_passagerid

Je ne vois donc pas pourquoi il n'utilise pas d'index dans dpr_passager !

Merci!

[CinePhil]

Je vois que la cardinalité de la clé étrangère fk_dpr_pass_reference_dpr_doss est la même que celle de la clé primaire.
Cela signifie t-il qu'il y a une ligne de la table référencée pour une ligne de la table dpr_passager ?

Et puisque cette clé étrangère semble référencer dpr_dossier_production, avez-vous essayé de faire une jointure directement entre dpr_passager et dpr_dossier_production ?

Pour la table alias "pp" (là par contre j'ai 2 colonnes dans la PK ...)

OK, c'est normal mais je ne vois pas d'index propre sur la seconde colonne de la clé primaire (rid_prestationtspid) dans votre capture d'écran. En avez-vous un ?

En effet, lorsque vous avez un index double, la seconde colonne n'est pas indexée individuellement et une recherche sur cette colonne seule n'utilisera pas l'index de clé primaire.

Dans l'index de clé primaire, pour simplifier, les lignes seront classées par exemple ainsi :
rid_passagerid, rid_prestationtspid
1, 1
1, 5
1, 8
2, 2
2, 3
3, 6
3, 12
4, 4
...

On voit bien que la seconde colonne n'est pas ordonnée (2, 3 et 6 viennent après 8 et 4 après 12). C'est pour ça que pour une clé primaire double, il faut indexer séparément la seconde colonne de la clé primaire. Cas typique des tables associatives.

[Mi895]

J'ai ajouté un index sur la table "pp"

Code :

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

1
2
ALTER TABLE dpr_passager_prestation_tsp
ADD INDEX idx_test (rid_passagerid);

Ca ne change rien j'ai toujours un full scan !

[CinePhil]

rid_passager_id est déjà indexé puisque c'est la première colonne de l'index primaire !
C'est sur rid_prestationtspid qu'il faut ajouter un index s'il n'existe pas déjà.

Et vous n'avez pas répondu aux autres questions !

[Mi895]

Super merci!

Une chose qui m'échappe, le full scan se faisait sur la table pax, pourquoi l'ajout d'un index dans pp résout le pb ?

Code :

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

1
2
INNER JOIN dpr_passager_prestation_tsp pp on pp.rid_prestationtspid=tsp.prestationtspid 
INNER JOIN dpr_passager pax on pax.passagerid=pp.rid_passagerid

[CinePhil]

Les mystères de ce que SQLPro appelle "MySQMerde" !

[SQLpro]

Postez l'intégralité de votre requête. En particulier nous ne savons pas ce qu'il y a dans la clause SELECT. Ceci a une influence sur le planb de requête donc les performances.

A +