Discussion :

[superthx]

Bonjour,

J´ai 2 tables :

1. Lot_Client
2. Production

Dans la table Lot_client j´ai la colonne "Date_prepa" et "numero_lot".
Dans la table Production j´ai la colonne "Lot_client" et "Date_envoi".
Dans "Lot_client.numero_lot" et "Production.Lot_client" on a le meme numero.

Comment update toute la colonne "Production.Date_envoi" avec la date de "Lot_client.Date_prepa" en se basant sur "Lot_client.numero_lot" = "Production.Lot_client".
Merci

[escartefigue]

Bonjour,

Qu'avez-vous déjà essayé comme requête ? Quel en est le résultat ?

[superthx]

Bonjour,
J´ai envie de tester ceci :

Code :

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

1
2
3
4
5
UPDATE Production.Date_envoi
SET    
Lot_client.Date_envoi = Production.Lot_client
WHERE  
Lot_client.numero_lot = Production.Lot_client

Mais j´ai un doute de flinguer la base de donnée.
Updater la colonne Date_envoi dans Production, ou Lot_client.numero_lot = Production.Lot_client.
D´ou m´a demande avant de faire clique

[escartefigue]

Il faut faire une jointure pour vérifier l'égalité du numéro de lot :

Code SQL :

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

1
2
3
4
5
update Production  PR
inner join Lot_Client LC
   on LC.numero_lot=PR.Lot_client
set PR.Date_envoi=LC.Date_prepa
;

Cette requête modifie potentiellement toutes les lignes de la table "Production", il faut éventuellement ajouter une restriction WHERE pour n'impacter que certaines lignes.

Note : c'est ballot d'avoir choisi un nom de colonne (Lot_client) dans une table qui correspond au nom d'une autre table...

[superthx]

Merci @escartefigue.
Mais en lançant la requête pour tester, j´ai cette erreur :

Code :

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

1
2
> 1205 - Lock wait timeout exceeded; try restarting transaction
> Time: 50,241s

comment puis-je y remedier ?

Peux t´on le faire por lots de lignes ?
Car j´ai aux alentours de 120000 lignes

[escartefigue]

C'est plutôt lent comme exécution pour seulement 120 000 lignes.
Est-ce que les colonnes de jointure sont bien de même type et de même longueur ?

Si c'est le cas, on peut commencer par réorganiser les tables (OPTIMIZE TABLE), puis, si toujours KO, procéder par plages de lots en ajoutant une restriction WHERE

[superthx]

Je vais verifier. Entre temps j´ai augmenter innodb_lock_wait_timeout mais je vais check ce que vous dites.
Je reviens d`s que je peux.

[superthx]

Je viens de verifier:
LC.numero_lot= VARCHAR 50
PR.Lot_client=VARCHAR 50
PR.Date_envoi= DATE
LC.Date_prepa= DATE
Donc oui même type et longueur apparement

[Artemus24]

Salut à tous.

Code :

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

1
2
> 1205 - Lock wait timeout exceeded; try restarting transaction
> Time: 50,241s

comment puis-je y remédier ?

Vous avez un problème de timeout. Il suffit d'augmenter la valeur de ce timeout :

Code :

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

1
2
3
4
5
6
7
8
9
# ------------------------------#
#     Timeout Configuration     #
# ------------------------------#
 
connect-timeout     = 600
interactive-timeout = 600
net-read-timeout    = 600
net-write-timeout   = 600
wait-timeout        = 600

600 secondes = 60 x 10. Soit 10 minutes.

A mettre dans votre fichier "my.ini".
Vérifiez avant la valeur que vous avez pour ces variables.

Code :

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

1
2
3
4
5
6
7
8
9
mysql> select @@global.connect_timeout;
+--------------------------+
| @@global.connect_timeout |
+--------------------------+
|                      600 |
+--------------------------+
1 row in set (0.00 sec)
 
mysql>

Attention au tiret qui devient un souligné.

La lenteur peut s'expliquer si vous n'avez pas fait l'usage des index sur les colonnes de votre jointure :

Code :

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

on LC.numero_lot=PR.Lot_client

Cordialement.
Artemus24.
@+

[superthx]

Bonjour!
En effet j´avais modifier le temps, mais cela etait trop long.
Donc j´ai indexer les deux colonnes.
Resultat moins d´une minute pour les 200000 lignes.
Merci à vous deux.

[escartefigue]

Toutes les colonnes déclarées PK sont automatiquement indexées
Et toutes les colonnes FK devraient également l'être, faute de quoi, la plupart des jointures provoqueront des temps de traitement rédhibitoires.
MySQL crée automatiquement l'index FK, du coup, sauf à l'avoir supprimé manuellement, je suis étonné que cet index était manquant.
(il me semble d'ailleurs que MySQL devrait protester en cas d'absence de cet index, à vérifier)

[Artemus24]

Salut Escartefigue.

La seule explication possible si l'index de la clef étrangère est absente, est que la clef étrangère n'existe pas en tant que telle.

J'ai vérifié. A partir du moment où dans la table fille, on crée une contrainte de type clef étrangère, MySql crée bien un index.

Cordialement.
Artemus24.
@+

[escartefigue]

J'ai vérifié. A partir du moment où dans la table fille, on crée une contrainte de type clef étrangère, MySql crée bien un index.

Oui pas de doute sur ce point, je me demande seulement comment se comporte MySQL si on drop l'index FK, volontairement ou non.
Je suppose que MySQL va couiner, mais je n'en suis pas certain

[Artemus24]

Salut Escartefigue.

Si après avoir créé la clef étrangère, tu désires supprimer l'index, Mysql va provoquer une erreur :

Code :

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

#1553 - Cannot drop index 'FK_01': needed in a foreign key constraint

Cordialement.
Artemus24
@+

[superthx]

Bon je sais que suis mauvais sur mysql.
Mais pourquoi il a fallu mettre index dans mes colonnes ?
Vous avez un texte expliquant cela ?
Sur les key, index, etc... ?

[Artemus24]

Salut à tous.

Bon je sais que suis mauvais sur mysql.

Dans ce cas là, vous devez lire la documentation sur MySql et apprendre en faisant des exercices.

Mais pourquoi il a fallu mettre index dans mes colonnes ?

Vous ne devez pas ajouter un index à votre clef étrangère.

Vous devez effectuer la bonne déclaration dans votre table fille, qui est la suivante

Code :

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

CONSTRAINT `FK_01` FOREIGN KEY (`clef`) REFERENCES `mere` (`mere_id`) ON DELETE CASCADE ON UPDATE CASCADE

MySql sait créer l'index à partir de la contrainte sur la clef étrangère.

Pour définir une clef étrangère, vous avez besoin de deux tables :

a) la table mère aura un identifiant qui sera unique.
Soit en tant que clef primaire, comme ci-après :

Code :

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

`mere_id` integer unsigned NOT NULL auto_increment primary key

ou soit en tant qu'index unique si cette colonne n'est pas déja une clef primaire :

Code :

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

CREATE UNIQUE INDEX `idx` USING BTREE ON `mere` (`mere_id`);

b) la table mère doit être déclarée avant la table fille.

c) la table fille aura une colonne servant de clef étrangère.
Cette colonne devra avoir la même déclaration que celle de la table mère.

d) la déclarative dans la table fille est la suivante :

Code :

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

1
2
`clef` integer unsigned NOT NULL
CONSTRAINT `FK_01` FOREIGN KEY (`clef`) REFERENCES `mere` (`mere_id`) ON DELETE CASCADE ON UPDATE CASCADE

e) il n'y a pas obligation que la clef étrangère se nomme du même nom que la clef servant de référence.

f) quand vous insérez une nouvelle ligne dans la table fille, la colonne servant de clef étrangère aura une valeur dont celle-ci sera au préalable déjà insérée dans la table mère.
Sans cela, l'insertion sera rejetée.

Vous avez un texte expliquant cela ?

--> FOREIGN KEY Constraints

Cordialement.
Artemus24.
@+

[escartefigue]

Bon je sais que suis mauvais sur mysql.
Mais pourquoi il a fallu mettre index dans mes colonnes ?
Vous avez un texte expliquant cela ?
Sur les key, index, etc... ?

Bonjour,

S'il n'y a pas d'index, les recherches se font séquentiellement en parcourant toute la table
Pour une table peu chargée, par exemple de quelques milliers de lignes, c'est indolore.
Pour des tables fortement chargées, le temps de recherche devient significatif
Si en plus, il y a une jointure, alors le nombre de ligne parcourues est égal au produit cartésien de l'effectif de chacune des tables.
Ce faisant, même avec un effectif modeste, le nombre de lignes à parcourir en l'absence d'index peut devenir très important.

Exemple : jointure entre 3 tables ayant respectivement 5 000, 10 000 et 20 000 lignes, ce qui est très peu pour une base de données.
Faute d'index, le nombre de lignes à parcourir serait de 5 000 x 10 000 x 20 000 = 1 000 000 000 000 ! (mille milliards de lectures !)

Imaginez devoir trouver un numéro de téléphone dans un annuaire qui ne serait trié ni par département, ni par nom... Bon courage !

[superthx]

Ok mais alors quel est la différence entre

• Key
• Unique
• Fulltext
• Spatial

Aussi une fois mis un index peut t´on l´enlever ?

[SQLpro]

... Et toutes les colonnes FK devraient également l'être, faute de quoi, la plupart des jointures provoqueront des temps de traitement rédhibitoires....

Non, ça c'est particulièrement idiot et sert à pas grand chose.... D'ailleurs l'indexation automatique des FK est une imbécilité de plus de MySQmerde. Aucun autre SGBDR ne fait cela !

Regardez le commentaire que nj'ai fait à ce sujet dans stackexchange...

https://softwareengineering.stackexc...-good-practice

A +

[Artemus24]

Salut à tous.

Sous Mysql, la primary key est automatiquement indexée.
En allant sous phpmyadmin, on voit l'index puisqu'il porte le nom par défaut de "PRIMARY".

J'ai un vague souvenir que sous DB2 gros système, l'index primary key n'est pas automatiquement créé.
Je crois qu'il faut faire explicitement la déclaration de la création de l'index pour l'obtenir.

Quand on crée la contrainte sur la "foreign key", Mysql va créer automatiquement un index.
Je ne vois pas trop à quoi peut servir cet index, puisque normalement la recherche est faite sur la table référencée, c'est-à-dire sur l'identifiant de la table mère.
La clef étrangère servant en tant que foreign key est juste pour faire le lien.

Je me pose la question si mysql ne gère pas le sens inverse, à savoir partir de la clef primaire de la table mère pour rechercher les clefs étrangères dans la table fille.
Sauf que la plupart du temps, cette recherche en sens inverse n'a pas lieu.
Une autre idée serait d'interdire dans la foreign key, le marquage à NULL.

Je remarque que mysql abuse un peu trop souvent des index, pour résoudre certains problèmes qui n'ont pas lieu être.
Par exemple, supprimer les doublons et l'on va créer un "unique index" sur la colonne".

Cordialement.
Artemus24.
@+