Discussion :

[Papy214]

Bonjour

Je teste une requête de mise à jour sur une table de 18 colonnes tout à fait standards (chaînes et valeurs numériques) et de 400 000 lignes.

Code SQL :

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

update `matable` set `monchamp` = 0 where `monindexprimaire` <> 0;

Résultat en local : 13 secondes

Je trouve ça très long mais peut-être est-ce normal ?

Pouvez-vous me confirmer que c'est normal ou si il y a un problème éventuel ?

[binarygirl]

Il faudrait montrer le schema complet de la table, et un plan d'exécution (utiliser la commande EXPLAIN).

[escartefigue]

Bonjour,

Le prédicat "différent de" (<>) est dit "non sargable", ça signifie qu'aucun index ne peut être utilisé pour ce type de critères.

En effet, rechercher une valeur égale, supérieure, inférieure, commençant par... permet de passer par un index, mais pour vérifier qu'une valeur est différente de celle choisie, il faut parcourir toutes les lignes de la table !

C'est ce qui explique que ce type de restrictions ne sera jamais performant dès que la population de la table est significative.

Pour information, dans une table d'un SGGBD relationnel, il n'y a pas de champs, mais il y a des colonnes.

[Papy214]

Ah d'accord !
Sachant que les valeurs ne peuvent pas être négatives, si j'utilise plutôt " > 0 " ça devrait donc être plus efficace ?

Pour colonne au lieu de champ, c'est vrai, mea culpa.

[escartefigue]

Si toutes les valeurs sont supérieures à zéro, ajouter une restriction WHERE ma_PK > 0 ne servira à rien non plus.
En effet, une restriction n'est pertinente pour ce qui concerne les performances que si son facteur de filtrage (% de lignes récupérées) est bon (de l'ordre de 10% maxi en général).

Vu qu'ici cette restriction renvoie toutes les lignes, passer par l'index serait contre performant : le moteur lirait l'index pour rechercher l'adresse des data autant de fois qu'il y a de lignes dans l'index. Donc autant parcourir séquentiellement toutes les data, ce sera plus rapide.

Donc dans ce contexte, là non plus, l'optimiseur ne choisira pas de passer par l'index (sauf cas particulier d'index couvrant, mais là c'est une autre histoire, sans rapport avec le facteur de filtrage )

[tbc92]

Est-ce que la colonne 'monchamp' est indexée elle aussi ?
Si oui, ça peut expliquer le temps relativement long. En noircissant un peu beaucoup le tableau, à chaque ligne modifiée, le moteur remet à jour l'index. Et plus on avance, plus il galère à remettre à jour l'index.

Mais de toutes façons, un update est toujours long.
Un 'Copy' dans une autre table vide, puis un Drop et Rename est souvent plus rapide... mais ça pose des tas d'autres problèmes.

[Séb.]

Code SQL :

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

update `matable` set `monchamp` = 0 where `monindexprimaire` <> 0;

Résultat en local : 13 secondes

Je trouve ça très long mais peut-être est-ce normal ?

Pouvez-vous me confirmer que c'est normal ou si il y a un problème éventuel ?

13 secondes pour modifier 400 k lignes, ça ne me choque pas.

[binarygirl]

Mais ça pourrait peut-être aller plus vite, d'où l'importance de sortir le plan d'exécution et de reformuler la requête le cas échéant.
Si le prédicat n'est pas "sargable", cette requête est manifestement non optimisée.

On peut facilement tester différents scenarii: il suffit faire un select en reprenant les critères de sélection formulés de différentes manières, et on peut ajouter la commande EXPLAIN devant le select, à moins d'avoir un autre outil plus friendly pour afficher le plan d'exécution.

[Artemus24]

Salut Papy214

Essayes cette requête :

Code mysql :

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

update `matable` set `monchamp` = case when `monindexprimaire` > 0 then 0 else `monchamp` end;

Cordialement
Artemus
@+

[escartefigue]

@Artemus24 : comme je l'ai expliqué plus haut, puisque la PK a systématiquement une valeur > 0 , ajouter une restriction de ce genre n'a aucun intérêt, car non filtrante.

[SQLpro]

13 secondes pour modifier 400 k lignes, ça ne me choque pas.

Le seul moyen qui permet de rendre une telle requête rapide serait le multithreading. Hélas MySQMerde ne le supporte pas, contrairement à DB2 ou SQL Server ou encore Oracle avec le module Parallel Query payant...

A +