Temps d'éxécution requête mysql vraiment contre intuitif

[swakone]

Bonsoir à tous !

Je viens de m'apercevoir de quelque chose de vraiment surprenant sur un de mes scripts php, si quelqu'un a une explication, je suis preneur avec grand plaisir.

Je fais deux requêtes identiques sur une vue contenant 750 lignes.

La seule différence que je rajoute à l'une d'entre elle :

WHERE .... and (colonne='0' or colonne='1')

colonne prend uniquement ses deux valeurs (true / false), donc clairement le filtre ne change rien au point de vue résultats de la requête. De plus ce n'est pas une colonne indexée.

La différence est surprenante :

Temps après requete sans le filtre => 3.6894 secondes.
Temps après requete avec le filtre => 0.0075 secondes.

je mesure juste avant et après le $result = mysql_query($sql);

Auriez vous une piste ?
Savez vous si ce comportement est identique lorsque le volume de données devient bien plus important ?

Merci et bonne nuit

 

[MarvinLeRouge]

Salut,

J'avoue être très étonné de ton résultat, d'autant plus que tu utilises une chaîne de caractères pour ton filtre, et que je suppose donc qu'il y a plus de difficulté de comparaison qu'avec un booléen sous forme de tinyint(1) (qui semblerait logiquement s'imposer ici).

Alors :
1) un temps d'exécution de plus de 3s semble étrange, vérifie ta requète à mon avis
2) si le moteur de mysql a, en interprétant ta requète avec filtre, obtenu le même résultat que suite à l'interprétation de ta requète sans filtre, et s'il n'y a pas eu trop de temps entre les 2, je suppose qu'il peut y avoir une histoire de cache là-dessous. Tu pourrais le vérifier en faisant quelque chose du genre sans filtre puis avec filtre dans une boucle, et en mesurant le temps à chaque fois. Ou en supprimant le cache mysql et en recommençant la manoeuvre.

 

[arnaudmn]

Bonjour,

Auriez vous une piste ?

Un index sur le champ Colonne peut-être ?
Faire "explain select ...." et tu verra ce qui change.

 

[swakone]

Bonjour,

merci pour vos réponses.

@arnaudmn => non il n'y a pas d'index sur cette colonne.

@MarvinLeRouge => j'utilise une chaine ? c'est à cause des guillemets ? ma colonne est de type INT (je sais ce n'est pas optimal comparé à un TINYINT... mais je crée mes tables avec un script et je n'ai pas encore prévu tout ça)

Je ne pense pas qu'il s'agisse d'une question de cache, j'ai fait 5 fois le test, avec un rand() pour l'ordre, voici le résultat :

Temps requete 2 3.4388 secondes.
Temps requete 1 0.0045 secondes.

Temps requete 2      3.4633 secondes.
Temps requete 1      0.0042 secondes.

Temps requete 2      3.3602 secondes.
Temps requete 1      0.0044 secondes.

Temps requete 1      0.0041 secondes.
Temps requete 2      3.3235 secondes.

Temps requete 2      3.3551 secondes.
Temps requete 1      0.0042 secondes.

Temps requete 1      0.0045 secondes.
Temps requete 2      3.5478 secondes.

La requete 1 est celle avec la condition inutile...

Je vais regarder du côté de explain select, je ne m'en sers jamais...

Je suis toujours autant surpris !

 

[jcaron]

Explain select te montrera très certainement deux plans très différents. Maintenant, pourquoi il choisit ce plan efficace dans un cas et pas dans l'autre, ce sera difficile à dire sans le schéma et le reste des conditions, surtout que tu nous dis que la requête porte sur une vue (donc les conditions que tu donnes s'ajoutent à celles déjà présentes dans la vue).

Jacques.

 

[MarvinLeRouge]

Pour les guillemets, si ta colonne est bien un int, tu n'en as tout simplement pas besoin. Mais ça ne compte pas ici, puisque c'est la requête la plus rapide apparemment.

Essaie plutôt de faire 100 fois r1 puis r2, et 100 fois r2 puis r1; et là regarde si la requête exécutée après l'autre est systématiquement plus rapide, ou s'il s'agit du contenu de la requête en lui-même.

 

[Leonick]

pour moi c'est clairement du à une forte charge serveur. regarde entre ton 2° et 3° test, ainsi qu'entre le 4° et le 5°, les temps d'exécution sont carrément inversés.
Si on veut un temps moyen d'exécution, c'est sur plusieurs centaines de tests qu'il faut faire l'essai

 

[jcaron]

L'ordre des requêtes est lui aussi inversé, ce sont donc bien les requêtes...

Avec un écart de cet ordre de magnitude, pas vraiment nécessaire de faire des centaines de requêtes pour confirmer... Il y a clairement une différence de plan d'exécution, dans un cas il n'utilise que des index, dans l'autre il scanne une (grosse) table d'un bout à l'autre, c'est quasiment garanti.

Jacques.

 

[swakone]

Bonjour à tous,

j'ai fait un explain select (que je ne connaissais pas...) et a priori, mysql ne découpe pas les requêtes dans le même ordre :

La vue est faite à partir de 4 tables.

L'ordre des deux premières tables est inversé d'une requête à l'autre, et surtout (a priori, ça viendrait de là ?) le type de référence faite :

pour la requête la plus rapide, il scanne une table et fait des référence type "eq_ref" sur les 3 autres tables, et pour la seconde requête, il scanne une table (différente) et fait 1 référence "ref" sur un des 3 tables, puis 2 "eq_ref". Après avoir lu un peu la doc de mysql, a priori "ref" serait (bien ?) plus longue a exécuter que "eq_ref".

Ce que j'ai du mal à comprendre c'est pourquoi l'ordre est différent...

Pour info, les deux tables sur lesquelles portent cet échange ont une relation 1:n.
Pour la requête la plus rapide, il lit la table "1" (en-tête) en premier, et pour l'autre requête il lit la table "n" (lignes) en premier. J'espère que c'est clair...

Je ne saurais pas expliquer ce comportement, mais j'aimerais être sûr que je peux me baser sur la fiabilité de ce comportement pour pouvoir optimiser ma requête... Donc si vous avez des infos je suis preneur !

Bonne après midi !

 

[jcaron]

J'avoue que je ne connais pas les détails de mysql (moi je suis plutôt postgresql), mais quand tu lances une requête, il y a généralement de nombreux moyens d'arriver au résultat (des "plans d'exécution"). La première chose que fait le serveur sql c'est d'essayer de trouver la méthode (le plan) dont il pense qu'elle est la plus rapide. Dans le cas de postgresql, ça veut dire qu'il va déterminer les "plans" possibles, et va calculer pour chacun le "coût" de ce plan, en se basant entre autres sur les stats qu'il a sur chacune des tables: taille, index disponibles, répartition des valeurs dans une colonne, etc.

Forcément, ça leur arrive de se tromper (par exemple dans ton cas, il arrive à la conclusion erronée que scanner la table complète est plus rapide alors que ce n'est visiblement pas le cas, et la condition supplémentaire le fait changer d'avis), et quelques changements mineurs peuvent le faire changer d'avis sur le plan à exécuter.

Non, je ne pense pas que tu puisses compter sur ce comportement. Dans le cas de postgresql, tu peux influencer son choix en changeant le niveau de granularité des stats qu'il conserve sur chaque table/colonne, et tu peux lui dire que tu veux vraiment qu'il évite les scans séquentiels. Le fait d'exécuter la requête directement, plutôt que via un prepare qui utilise des placeholders, peut aussi le faire changer d'avis radicalement. Je ne sais pas comment on influence mysql, mais je suppose qu'il existe des mécanismes similaires, je laisse la parole aux spécialistes...

Jacques.