Discussion :

[pelloq1]

Bonjour,

J'ai eu un soucis de performances avec une requête. J'ai pu le contourner, mais j'aimerai comprendre le pourquoi.

Requête originale:

Code :

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

SELECT count(*) FROM abc_reponse WHERE id_question IN (SELECT id_question FROM abc_test_result WHERE id_test=482)

Fait planter mon code php avec une erreur 500 ou planter phpmyadmin, ensuite si je rafraîchi la page, c'est quasi instantané. Donc ce n'est pas une erreur au niveau des données passées à la requete.

Requete corrigée:
Maintenant j'ai décomposé la requete en 2:

Code :

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

1
2
3
4
5
6
SELECT id_question FROM abc_test_result WHERE id_test=482;
 
//... code php ... pour en retourner une chaine des ids récoltés ci-dessus... 
$s_ids = implode(',', $ids);
 
SELECT count(*) from abc_reponse where id_question IN ({$s_ids})

Et la c'est instantané.

J'aimerai simplement comprendre le pourquoi du non fonctionnement de la première.

Je peux fournir la structure des tables si nécessaire.

Merci, Cédric

[al1_24]

Il faudrait avant tout connaître l'erreur retournée par MySQL pour pouvoir essayer de comprendre l'origine du problème.
Y a-t-il des index sur ces tables ?
As-tu essayé avec EXISTS ?

Code :

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

1
2
3
4
5
6
7
8
SELECT  COUNT(*)
FROM    abc_reponse rep
WHERE   EXISTS
        (   SELECT  NULL
            FROM    abc_test_result tst
            WHERE   tst.id_question = rep.id_question
                AND tst.id_test     = 482
        )

[pelloq1]

Merci de ta réponse.

Il n'y a pas d'erreur mySql, juste un temps d'execution très long:

Code :

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

1
2
3
4
5
6
7
8
9
SELECT COUNT( * ) 
FROM abc16_aborc_reponse rep
WHERE EXISTS (
 
SELECT NULL 
FROM abc_test_result tst
WHERE tst.id_question = rep.id_question
AND tst.id_test =482
)

Showing rows 0 - 0 (1 total, Query took 96.5143 sec)



Les 2 tables on un index:

Code :

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

1
2
3
4
5
6
7
8
9
10
SHOW INDEX FROM 
0
PRIMARY
1
id
A
5685
NULL
NULL
BTREE

Je vois pas....

[al1_24]

Et avec un index sur abc_test_result(id_test, id_question) et un autre sur abc_reponse(id_question) ?

[Artemus24]

Salut pelloq1.

Code :

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

SELECT count(*) FROM abc_reponse WHERE id_question IN (SELECT id_question FROM abc_test_result WHERE id_test=482)

Je tiens à signaler que l'usage de la clause "in" est lié à un paramétrage de votre fichier "my.ini".
Le fait de mettre la clause "in" implique que MySql va devoir stocker le résultat de votre sous-requête dans un fichier temporaire.
Et si ce fichier temporaire est trop petit, cela va faire buguer votre requête.

Combien d'éléments sont retournés dans votre sous-requête "SELECT id_question FROM abc_test_result WHERE id_test=482" ?

D'autre part, il se peut que vous avez des doublons. Alors pourquoi les selectionner aussi. Autant mettre ceci :

Code :

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

SELECT count(*) FROM abc_reponse WHERE id_question IN (SELECT distinct id_question FROM abc_test_result WHERE id_test=482)

Même question que précédemment, en mettant un distinct ?

Même si ce que vous faites n'est pas une erreur en soit, en général on utilise la clause "in" avec une liste de valeurs en dure.
Par exemple "[c]where id_test in (333, 482, 525)".

Dès que le nombre d'éléments dans la liste devient important, on ne procède plus avec la clause "in" mais avec un "exists".
La bonne façon de faire est la solution proposé par al1_24.

Il n'y a pas d'erreur mySql, juste un temps d'execution très long:

Le temps d'exécution est trop long car vous devez balayer vos deux tables.
Pour résoudre ce genre de problème, vous devez créer un index sur la colonne "id_test" de la table "abc_test_result".
Et aussi un autre index sur la colonne "id_question" de la table "abc_reponse".

@+

[pelloq1]

Merci à ceux qui ont pris le temps de répondre.

@al1_24 -> Merci!

Et avec un index sur abc_test_result(id_test, id_question) et un autre sur abc_reponse(id_question) ?

Désolé je ne sais pas faire ça. Je ne peux pas modifier la structure des données.

@Artemus24 -> Merci!

Combien d'éléments sont retournés dans votre sous-requête

Il n'y pas de doublon possible, et il n'y a jamais plus de 50 résultats.


cedric

[escartefigue]

Désolé je ne sais pas faire ça. Je ne peux pas modifier la structure des données.

En ce cas faites la demande auprès de votre DBA

Il n'y pas de doublon possible, et il n'y a jamais plus de 50 résultats.

Ce n'est pas tant le nombre de résultats que l'effectif total de chacune des tables utilisées dans la requête qui pilotent la performance.

[Artemus24]

Salut à tous.

Je reprends l'exemple d'al1_24. Voici comment créer un index :

Code :

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

CREATE INDEX `idx` USING BTREE ON `abc_test_result` (`id_test`,`id_question`);

Il n'y pas de doublon possible, et il n'y a jamais plus de 50 résultats.

C'est pas énorme. Je m'attendais à plusieurs centaines de valeurs.

Ce n'est pas tant le nombre de résultats que l'effectif total de chacune des tables utilisées dans la requête qui pilotent la performance.

Désolé, mais peux-tu me traduire cela car je ne comprends.

Ce n'est pas le nombre de lignes du ou des tables qui déterminent la performance.
Mais plus dans la façon d'accéder à ces lignes, d'où l'intérêt de mettre des index pour ne pas faire un balayage de la totalité de la table.

@+

[escartefigue]

Désolé, mais peux-tu me traduire cela car je ne comprends.
Ce n'est pas le nombre de lignes du ou des tables qui déterminent la performance.
Mais plus dans la façon d'accéder à ces lignes, d'où l'intérêt de mettre des index pour ne pas faire un balayage de la totalité de la table.

Evidemment que les index peuvent être utiles !
ils peuvent aussi ne servir à rien car non discriminants et en ce cas nuire aux perfs lors des insert/update/delete

Mais je répondais à

Il n'y pas de doublon possible, et il n'y a jamais plus de 50 résultats.

Je confirme donc, d'une manière générale, c'est beaucoup moins l'effectif de la table finale résultante du select qui pilote les performances que l'effectif des tables consultées (inclus dans certains cas les tables intermédiaires, via subselect ou CTE par exemple)

[pelloq1]

Donc, dans mon cas il est préférable d'utiliser EXISTS au lieu de IN ?

Aussi dans les cas ou c'est une liste 'fixe' de valeur?

Car j'ai des cas ou j'ai plusieurs centaines de valeurs.

Cédric

[escartefigue]

A chaque fois que c'est possible, il est préférable d'utiliser (NOT) EXISTS plutôt que (NOT) IN (subselect)
Car IN crée une table résultante et donc utilise de l'espace mémoire et charge le réseau, pour autant de lignes qu'il y a de réponses alors que EXISTS ne transporte aucune colonne, et répond par un booléen

Quand l'utilisation d'EXISTS n'est pas possible
- si la liste de valeurs possibles est fixe et qu'il y en a peu, utilisez (NOT) IN
- si la liste de valeurs possibles est variable ou qu'il y en a beaucoup, utilisez une requête imbriquée

Il existe aussi d'autres solutions : jointure outer avec test de nullité, EXCEPT...

[Artemus24]

Salut à tous.

ils peuvent aussi ne servir à rien car non discriminants et en ce cas nuire aux perfs lors des insert/update/delete

Oui, tu as raison car dans certains cas, mettre un index peut rallonger le temps d'exécution de la requête.
Mais il ne faut pas aussi oublier le "select" qui est aussi impacté !

c'est beaucoup moins l'effectif de la table finale résultante du select qui pilote les performances que l'effectif des tables consultées (inclus dans certains cas les tables intermédiaires, via subselect ou CTE par exemple)

Tu as beau reformuler ta phrase différemment, j'ai du mal à la comprendre.

La performance (je parle du select) est due essentiellement aux nombres de lectures physiques effectuées pour récupérer les données.

Donc, dans mon cas il est préférable d'utiliser EXISTS au lieu de IN ?

OUI !

Aussi dans les cas ou c'est une liste 'fixe' de valeur?

Cela dépend du nombre de valeurs, mais je dirais que vous pouvez utilisez la clause "in".

La question est de savoir pourquoi avez-vous en dure dans vos requêtes, une liste 'fixe' de valeur ?
Ne serait-il pas plus judicieux de les mettre dans une table ?

@+

[escartefigue]

Evidemment que les index peuvent être utiles !
ils peuvent aussi ne servir à rien car non discriminants et en ce cas nuire aux perfs lors des insert/update/delete

Oui, tu as raison car dans certains cas, mettre un index peut rallonger le temps d'exécution de la requête.

Mais il ne faut pas aussi oublier le "select" qui est aussi impacté !

Non, les index inutiles ne pénalisent que les ordres de mise à jour, les requetes SELECT ne sont pas pénalisées par les index inutiles.
Les index inutiles pénalisent aussi les servitudes (sauvegardes, restaurations, réorgs...) et l'espace disque, mais c'est un autre sujet

La performance (je parle du select) est due essentiellement aux nombres de lectures physiques effectuées pour récupérer les données.

En effet, c'est bien pourquoi, toutes choses égales par ailleurs, plus il y a de lignes dans les tables, plus il y a de lectures à faire.
Bien évidemment, si la requete SELECT correspond à un index unique et éligible, alors on se fout du volume (quasiment, dans les
faits ce n'est pas tout à fait vrai, puisque la profondeur de l'index change, mais là on joue sur des nanosecondes
Si par contre, la requete select ne trouve pas d'index éligible ce sera le volume dans les tables qui fera toute la différence, et ce que la requête restitue 1 ou 3 milliards de lignes en sortie (là aussi je simplifie, restituer 3 milliards de lignes, ce n'est pas neutre )
Enfin, à contrario, si l'on fait un table scan d'une table qui n'a qu'une seule ligne, le temps de réponse est immédiat, et même plus rapide que d'utiliser un éventuel index

[Artemus24]

Salut Escartefigue.

Non, les index inutiles ne pénalisent que les ordres de mise à jour, ...

Je suis d'accord.

... les requêtes SELECT ne sont pas pénalisées par les index inutiles.

Tout dépend comment l'optimiseur va s'y prendre pour choisir la meilleure stratégie.
Sur ce point, je ne suis pas aussi catégorique que vous.

Si vous entendez par "index inutiles", le fait que l'optimiseur ne choisisse pas l'index, alors je suis d'accord.
Mais parfois, c'est le contraire qui se produit, l'optimiseur ne choisit pas l'index alors qu'il aurait dû le faire.
Et dans ce cas, il faut forcer cette décision.

Si tout ce passait bien, la "plan table" n'existerait pas !

@+

[SQLpro]

...parfois, c'est le contraire qui se produit, l'optimiseur ne choisit pas l'index alors qu'il aurait dû le faire.
Et dans ce cas, il faut forcer cette décision

C'est donc que l'optimiseur est mauvais. Si vous forcez le choix d'index vous rendez le plan statique alors qu'il doit rester dynamique car une base est "vivante" les données évoluant. En forçant l'optimiseur à adopter une stratégie plutôt que l'autre vous risquez plus gros encore... Que les performances deviennent subitement catastrophiques.

Je sais hélas que dans certains SGDR (Oracle en particulier) le cas est fréquent....

Ce n'est heureusement pas le cas partout. Par exemple c'est extrêmement rare avc SQL Server et interdit avec PostGreSQL (sauf Enterprise DB)

Encore une fois MySQMerde reste un outil pour bricoleur !

A +

[escartefigue]

Non, les index inutiles ne pénalisent que les ordres de mise à jour, les requetes SELECT ne sont pas pénalisées par les index inutiles.
Les index inutiles pénalisent aussi les servitudes (sauvegardes, restaurations, réorgs...) et l'espace disque, mais c'est un autre sujet

Tout dépend comment l'optimiseur va s'y prendre pour choisir la meilleure stratégie.

Sur ce point, je ne suis pas aussi catégorique que vous.

Si vous entendez par "index inutiles", le fait que l'optimiseur ne choisisse pas l'index, alors je suis d'accord.
Mais parfois, c'est le contraire qui se produit, l'optimiseur ne choisit pas l'index alors qu'il aurait dû le faire.
Et dans ce cas, il faut forcer cette décision.

D'une part il ne faut pas confondre index inutile et index inutilisé dans une requete

Exemple d'index inutile :
Sur une table des individus, je crée un index sur la colonne "code sexe" qui peut prendre 4 valeurs (pour homme, femme, non communiqué, non déterminé), cet index ne sera jamais utilisé comme argument de recherche et ce quelles que soient les requetes que je pourrai construire car 4 valeurs distinctes seulement ne sont pas suffisament discriminantes pour que cet index soit éligible.

Exemple d'index inutilisé :
Sur cette même table des individus, je fais une recherche par prénom ou par date de naissance ou tout autre colonne pour laquelle il n'y a pas d'index.
Les index existants ne seront pas utilisés, pour autant ils sont utiles à d'autres requêtes.

D'autre part, qu'un index soit utilisé, inutilisé ou inutile, encore une fois, il ne nuira jamais aux requetes SELECT
La stratégie d'accès faite par l'optimiseur n'a donc aucun rapport avec ce sujet

[aieeeuuuuu]

cet index ne sera jamais utilisé comme argument de recherche et ce quelles que soient les requêtes que je pourrai construire car 4 valeurs distinctes seulement ne sont pas suffisament discriminantes pour que cet index soit éligible.

Sauf si l'index est couvrant :
Par exemple, si on veut simplement connaitre la répartition de la population :

Code :

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

1
2
3
4
 
SELECT CodeSexe, COUNT(*)
FROM LaTable
GROUP BY CodeSexe

Ou bien encore connaitre le nombre d'hommes

Code :

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

1
2
3
4
 
SELECT COUNT(*)
FROM LaTable
WHERE CodeSexe = 'H'

On ne peut donc juger de la pertinence d'un index que par rapport à une requete donnée, et dans un contexte donné (fréquence et criticité de la dite requete, et cout de maintient de l'index en fonction des autres besoins...)

[escartefigue]

Sauf si l'index est couvrant :
Par exemple, si on veut simplement connaitre la répartition de la population :

NON !

Justement ce n'est pas pour rien que j'ai mis argument de recherche en gras
S'il n'est utilisé que par ce qu'il est couvrant, alors il est utilisé comme data et non comme argument de recherche , il est donc parcouru en ce cas séquentiellement (index scan)

[Artemus24]

Salut à tous.

Sur une table des individus, je crée un index sur la colonne "code sexe" qui peut prendre 4 valeurs

Il ne me viendrait jamais à l'esprit de créer un index uniquement sur la colonne "code sexe". Pourquoi ?
Car le nombre de valeurs que peut prendre cette colonne n'est pas assez important pour optimiser une requête.

Inversement, si la requête contient une sélection sur plusieurs colonnes, dont la colonne "code sexe", oui je pourrais la définir dans un index.
Mais vu le nombre de valeurs qu'elle peut prendre, ce n'est pas pertinent.

Sauf si je veux extraire disons trois colonnes de ma requête dont "code sexe", et que je désire optimiser l'accès afin de récupérer toutes les valeurs de ces trois colonnes à partir de l'index uniquement.

car 4 valeurs distinctes seulement ne sont pas suffisamment discriminantes pour que cet index soit éligible.

Sur ce point, nous sommes d'accord !

Toute la question demeure dans le nombre de valeurs possible que pourrait prendre une colonne candidate dans un index ?

En fait, je raisonne à l'envers. Si pour une valeur donnée, je n'ai jamais plus, disons de 10 tuples, alors c'est un excellent candidat en tant qu'index.

D'autre part, qu'un index soit utilisé, inutilisé ou inutile, encore une fois, il ne nuira jamais aux requêtes SELECT

Sur le inutile ou inutilisé, je dirais que c'est une lapalissade !

Mais quand est-il de l'influence des index utilisés par l'optimiseur sur les lectures ?

Il augmente le nombre de lectures, puisque nous sommes obligés, en plus de l'accès à la table, de passer par l'index.
Mais en même temps, il diminue les accès à la table, puisque nous ne faisons pas un scan de la totalité de la table, mais une lecture directe de la ligne pointée par l'index.
Donc oui, cela à une énorme influence sur les requêtes SELECT, sinon à quoi peut-il servir de mettre un index si cela n'influence pas les performances ?

Donc cela ne nuit pas "aux requête SELECT". Tout au contraire !

Je ne sais pas si tu le fais exprès Escartefigue, mais je trouve que tu joues sur les mots.

Sauf si l'index est couvrant :

C'est quoi un index couvrant ?

@+

[SQLpro]

C'est quoi un index couvrant ?

@+

Dommage que vous n'ayez toujours pas lu mon livre sur le langage SQL...

Alors lisez au moins les articles que j'écris... http://sqlpro.developpez.com/cours/quoi-indexer/#LVIII

A +