Pb UPDATE d'une table avec une autre table

**Korben-Dallas** · 04/02/2013, 17h04

Bonjour,

Je rencontre un problème de mise à jour de champs d'une table à l'aide d'une autre, et je ne sais pas comment le résoudre.
Merci pour votre aide.

Voici la structure des tables :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE TABLE `t_Table1` (
	`Gestionnaire` TEXT NOT NULL,
	`AdresseIP` VARCHAR(20) NOT NULL,
	`AdresseIPModifiee` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`ID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
AUTO_INCREMENT=31793;
 
 
 
CREATE TABLE `t_Table2` (
	`AdresseIP_UA` VARCHAR(16) NOT NULL,
	`AdresseIP_DNS` VARCHAR(200) NOT NULL,
	`Gestionnaire` VARCHAR(20) NOT NULL,
	`AdresseDebutPlage_traitee` VARCHAR(16) NULL DEFAULT NULL,
	`AdresseFinPlage_traitee` VARCHAR(16) NULL DEFAULT NULL,
	INDEX `DIT` (`DIT`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
AUTO_INCREMENT=5909;

Voici la requête que j'utilise mais celle-ci ne rend pas la main, elle tourne en boucle :

Code :

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

1
2
3
4
5
6
 
  UPDATE t_Table1 AS Reseaux, t_Table2 AS GestionSites
  SET Reseaux.Gestionnaire= GestionSites.Gestionnaire
  WHERE Reseaux.adresseIPModifiee BETWEEN inet_aton(GestionSites.AdresseDebutPlage_Traitee) AND inet_aton(GestionSites.AdresseFinPlage_Traitee)  
     OR Reseaux.adresseIPModifiee = inet_aton(GestionSites.AdresseDebutPlage_Traitee)  
     OR Reseaux.adresseIPModifiee = inet_aton(GestionSites.AdresseFinPlage_Traitee) ;

Le but est de mettre à jour le champ "Table1.Gestionnaire" à l'aide du champ "Table2.Gestionnaire" à condition que les adresses IP de la table1 soient égales ou dans la plage correspondante dans la table2

Merci donc pour votre aide précieuse.
Là je boucle aussi (pas que la requête

) pour corriger cette requête.

**semaphore1984** · 04/02/2013, 18h18

Bonjour,

je vois déjà deux problèmes dans ta requête :

1 ) si ta table t_table_1 est conforme au script, il n'y a pas de colonne qui s'appelle ARSIT donc "Reseaux.ARSIT " ne référence rien de connu ?
2) tu n'a pas de clause de jointure dans ta requête, il va donc y avoir un produit cartésien entre les 2 tables soit environ 188 millions d'enregistrements si j'en crois les id autoincrement !

De plus, je te conseillerait de faire toujours l'essai avec un SELECT avant de lancer un update, ça peut éviter de gros ennuis et de la perte de temps.
Tu peux même fait un select count(*) pour voir le nombre de records impactés.

Autre remarque, le BETWEEN inclu les bornes, donc les deux lignes de OR sont à mon avis inutiles.
Cordialement

**Korben-Dallas** · 04/02/2013, 19h00

OK Merci
En effet, il y a eu un mauvais copier/collé ce qui explique la première erreur que tu indiques.
Je corrige donc cela de suite.
Quand à la 2è remarque (tout à fait judicieuse :-)) sur les jointures, en fait je ne vois pas comment je pourrais joindre les 2 tables c'est pour cela que je fait un WHERE puisque les 2 tables sont entièrement dynamiques, les valeurs de l'une remplaçant les valeurs de l'autre et inversement (en fonction des demandes effectuées dans le logiciel)

**ericd69** · 04/02/2013, 19h24

salut,

en fait si il y a 3 condition de jointure mais cette vieille écriture d'il y a 20 ans est pénible à relire

pour mieux la visualiser utilisons la méthode de semaphore1984 qui est celle que je préconise toujours aussi pour tester un update avec jointure...

de même il a raison sur le between et les or, les bornes testées par between sont incluses c'est équivalent à <= et >= et non à < et > donc tes or sont, à priori, inutiles...

Code sql :

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

1
2
3
  UPDATE t_Table1 AS Reseaux, t_Table2 AS GestionSites
  SET Reseaux.ARSIT = GestionSites.Gestionnaire
  WHERE Reseaux.adresseIPModifiee BETWEEN inet_aton(GestionSites.AdresseDebutPlage_Traitee) AND inet_aton(GestionSites.AdresseFinPlage_Traitee) ;

ce qui donne la requête de test avec un select:

Code sql :

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

1
2
select count(*) from t_Table1 AS Reseaux, t_Table2 AS GestionSites
WHERE Reseaux.adresseIPModifiee BETWEEN inet_aton(GestionSites.AdresseDebutPlage_Traitee) AND inet_aton(GestionSites.AdresseFinPlage_Traitee);

ou en version moderne normalisée et un peu plus optimisée (l'optimiseur ne réfléchit pas pour savoir que tu fais une jointure)...

Code sql :

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

1
2
3
select count(*) from t_Table1 AS Reseaux
inner join t_Table2 AS GestionSites on
     Reseaux.adresseIPModifiee BETWEEN inet_aton(GestionSites.AdresseDebutPlage_Traitee) AND inet_aton(GestionSites.AdresseFinPlage_Traitee) ;

on a ainsi le nombre de lignes touchées...

ce qui devrait donner la requête finale:

Code sql :

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

1
2
3
4
UPDATE t_Table1 AS Reseaux
inner join t_Table2 AS GestionSites on
     Reseaux.adresseIPModifiee BETWEEN inet_aton(GestionSites.AdresseDebutPlage_Traitee) AND inet_aton(GestionSites.AdresseFinPlage_Traitee)  
SET Reseaux.ARSIT = GestionSites.Gestionnaire;

au passage, le or exclut l'utilisation d'index ce qui peut engendre un traitement plus long...

**semaphore1984** · 04/02/2013, 22h46

Bonjour korben-dallas et ericd69,

Oui, c'est vrai ericd69, on peut considérer qu'il s'agit d'une jointure, mais j'appellerai ça une "jointure lâche".

En effet, qu'est-ce qui garanti que les ensembles d'IP associées à chaque GestionnaireSite sont disjoints ?

Si ce n'était pas le cas, on aurait un risque d'update redondants avec un résultat imprévisible au final quand au contenu de la colonne Reseaux.ARSIT (ou plutôt Reseau.Gestionnaire, je suppose)

Par contre, si ces plages d'adresse sont bien disjointes, ça doit effectivement marcher comme l'indique ercid69

Cordialement

**ericd69** · 04/02/2013, 22h51

oui je suis d'accord... maintenant à lui de voir...

**Korben-Dallas** · 05/02/2013, 10h38

Bonjour à vous 2,

J'avais bien essayé avec la requête que vous indiquez, celle-ci est en effet plus propre (il n'y a pas photo) mais celle que j'ai indiquée ici permettait aussi d'expliquer plus facilement mon pb.
Mais bon là n'est pas mon pb.
Déjà j'ai appris que le BETWEEN gère les bords c'est déjà cool n'étant pas sûr moi-même.
Sinon je viens donc de mettre en production la requête modifiée en intégrant le INNER JOIN (ce qui est plus propre je vous l'accorde)
Mais le pb reste identique, la requête est longue à s'exécuter mais à priori c'est normal puisque vous confirmez que la requête est correcte.
Alors que la Table1 a environ 32000 enregistrements et la table 2 en a entre 1500 et 2000 (aujourd'hui, celle-ci évoluant dynamiquement en augmentant ou en diminuant le nombre d'enregistrements), il faut entre 4 et 6 min pour exécuter cette simple requête, d'où le fait que je pensais qu'il y avait un pb.
D'autres requêtes bien plus complexes mettent beaucoup moins de temps.

**CinePhil** · 05/02/2013, 10h59

Peut-être que la fonction inet_aton empêche l'utilisation d'un index ?
Mais déjà, les tables sont-elles indexées ?

Donnez-nous votre requête et le résultat de EXPLAIN [la requête]

**Korben-Dallas** · 05/02/2013, 10h59

Je viens de faire un test de la requête, ce n'est pas 4 à 6 minutes qu'il lui faut mais 10 bonnes minutes, ce qui me parait très long. Qu'en pensez-vous ?

**Korben-Dallas** · 05/02/2013, 11h03

c'est bien :

Code :

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

EXPLAIN UPDATE ......

???
En exécutant cette requête j'ai une erreur de syntaxe

**CinePhil** · 05/02/2013, 11h07

Arf ! EXPLAIN ne fonctionne qu'avec SELECT je crois !

**ericd69** · 05/02/2013, 11h35

oui fais le explain sur le select équivalent que je t'ai donné...

et oui inet_aton comme toute fonction va empêcher normalement l'utilisation d'un index, seuls les opérateurs le permettent...

la question est pourquoi appliquer cette fonction là et pas à l'insertion des données?

en gros on s'en sert pour compacter une ip et pas se trimbaler une chaine texte:

tu fais une comparaison directe de nombres au lieu d'une comparaison de chaine ou pire de ce que tu fais: une comparaison d'une conversion de chaine
plus de fonction de conversion donc gain de temps + index...
tu compactes une table qui devrait vite enfler

**Korben-Dallas** · 05/02/2013, 12h02

Requête :

Code :

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

1
2
 
EXPLAIN SELECT Reseaux.Gestionnaire, GestionSite.Gestionnaire FROM t_Table1 AS Reseaux INNER JOIN t_Table2 AS GestionSite ON Reseaux.AdressesReseaux_Traitee BETWEEN inet_aton(GestionSite.AdresseDebutPlage_Traitee) AND inet_aton(GestionSite.AdresseFinPlage_Traitee)

Résultat :

Code :

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

1
2
3
4
5
6
7
8
9
 
=================================================================================================================
| id | select_type |    table    | type | possible_keys |  key  | key_len  | ref  |  rows  |       Extra        |
=================================================================================================================
| 1  |    SIMPLE   | GestionSite | ALL  |      null     |  null |   null   | null |  1706  |                    |
-----------------------------------------------------------------------------------------------------------------
| 1  |    SIMPLE   |   Reseaux   | ALL  |      null     |  null |   null   | null |  31792 |Using where; Using  |
|    |             |             |      |               |       |          |      |        |    join buffer     |
-----------------------------------------------------------------------------------------------------------------

**ericd69** · 05/02/2013, 12h16

donc il fait un fullscan aucun index...

comme tu trimballes des lignes avec des conversions sur du texte avant de faire la jointure...

je serait de toi je reverrais sérieusement les choses tant que tu as l'air en phase de développement...

parce que là tu ne brasses pas encore grand chose...

**Korben-Dallas** · 05/02/2013, 12h18

OK Mais là que dois-je faire exactement selon vous ?
Je ne suis pas un expert MySQL, je m'en sers mais c'est tout.
Tout ça pour un petit outil de supervision logiciel propriétaire que j'ai créé.

**ericd69** · 05/02/2013, 13h08

recrée tes 2 tables en changeant la nature des colonnes où tu as des ip en int unsigned

place des index (double colonne sur celle où tu as début et fin et simple sur l'autre) sur tes colonnes avec ip

recopie tes tables originales en convertissant les ip avec inet_ntoa() en utilisant la syntaxe insert into ... select...

teste alors tes requête sur les nouvelles tables... déjà les select puis l'update...

si tu as bien un gain conséquent modifie tes requête initiales pour virer les inet_aton() et mets des inet_ntoa() dans celle qui insèrent et inet_aton() dans celles qui font de la restitution

drope tes 2 anciennes tables et renomme les nouvelles avec le nom des anciennes...

ça devrait être le plus simple je pense

**Korben-Dallas** · 05/02/2013, 14h40

Je n'ai pas tout compris dans la réponse

Envoyé par ericd69

place des index (double colonne sur celle où tu as début et fin et simple sur l'autre) sur tes colonnes avec ip

Peux-tu expliquer ceci ?
Merci

De plus, je pense qu'il est préférable de créer des colonnes en conséquence puisque les adresses IP ainsi que les autres données de ces tables sont injectées via des fichiers CSV. Et ce de façon dynamique et au cas par cas. Le Gestionnaire le faisant de lui-même.
C'est d'ailleurs ce que j'ai fait pour mes autres tables, et les requêtes attaquent les valeurs IP ainsi modifiées pour les traitements sauf pour leurs affichages où je prends directement le champs AdresseIP (ce qui évite une reconversion)
Je sais, vous me direz que d'avoir des valeurs en double (même si elles ne sont pas tout à fait en double puisque en clair et après traitement inet...) n'est pas top mais question espace disque ce n'est pas un problème.
Le principal problème étant que les traitements doivent être le plus rapide possible puisque j'ai des injections dans la BDD toutes les 30 min voire toutes les 15 min en fonction des heures.

**ericd69** · 05/02/2013, 15h14

tu peux mettre un index simple (sur une seul colonne) voir double (sur 2 colonnes)

au choix... ça peut ici être intéressant de mettre un index du genre (debut,fin) au lieu de (debut) et (fin) pour ta plage d'ip

**Korben-Dallas** · 05/02/2013, 15h22

OK Mais là on tombe justement dans quelque chose que j'ai encore bcp de mal à comprendre, les index.
Quelle est justement la différence entre avoir ?
- un index "Debut" sur la colonne "Debut" et un index "Fin" sur la colonne "Fin"
- un index Double "Debut-Fin" qui pointe sur les 2 colonnes "Debut" et "Fin"

**CinePhil** · 05/02/2013, 15h52

On va prendre un cas simple et schématique avec deux colonnes A et B + un identifiant de ligne :

Id / A / B
1 / 1 / 1
2 / 1 / 2
3 / 2 / 4
4 / 3 / 1
5 / 2 / 1
6 / 1 / 5
7 / 2 / 2
8 / 3 / 2

Index sur A :
A / Id
1 / 1
1 / 2
1 / 6
2 / 3
2 / 5
2 / 7
3 / 4
3 / 8

Index sur B :
B / Id
1 / 1
1 / 4
1 / 5
2 / 2
2 / 7
2 / 8
4 / 3
5 / 6

Index sur (A, B) :
A / B / Id
1 / 1 / 1
1 / 2 / 2
1 / 5 / 6
2 / 1 / 5
2 / 2 / 7
2 / 4 / 3
3 / 1 / 4
3 / 2 / 8

Si je cherche la ligne qui a A = 2 et B = 4, avec l'index sur les deux colonnes, je parcours un index jusqu'à extraire le bon Id qui correspond alors que si je n'ai que des index mono-colonnes, je cherche les lignes qui ont A = 2 (lignes 3, 5 et 7) puis les lignes qui ont B = 4 (ligne 3) puis je fais l'intersection des deux listes. C'est plus long.

Pour comparaison, pense à la manière dont tu cherches un mot dans le dictionnaire.
Si tu cherches "informatique", tu vas à la lettre i puis tu cherches les mots qui commencent par "in", puis par "inf"...
Si la seule manière d'accéder au mot cherché consistait à faire l'intersection de la liste des identifiants des mots contenant un i avec celle des mots contenant un n, celle des mots contenant un f... Ce serait trop galère !

Pb UPDATE d'une table avec une autre table

Requêtes MySQL

Discussions similaires

Partager

Partager