Discussion :

[fanfouer]

Bonjour,

Je cherche à mettre sur pied deux requêtes me permettant de consolider les valeurs présentes dans une colonne d'une de mes tables.

Cette table contient un nombre conséquent de lignes (environ 6 millions).
Parmi ces lignes, beaucoup (environ 5 millions) ne contiennent pas de valeur dans une des colonnes de cette table (ref_c, cf ci-dessous). Je compte définir cette valeur pour une majorité sinon l'ensemble des 5M sur la base des informations qu'ont en commun ces 5 millions de lignes incomplètes et le million restant, complet.

L'algorithme actuellement en place est basé sur la sélection des lignes incomplètes puis en la recherche d'une ligne complète pouvant convenir pour chacune puis enfin leur mise à jour.

Le nombre important de requêtes résultant augmente, je pense, beaucoup le temps de traitement, de l'ordre d'une journée actuellement.
L'algorithme est exécuté successivement au moins 3 fois car un seul passage n'élimine pas tous les trous.

Je souhaiterais mettre en place une seule requête, basée sur une jointure, qui réalise l'ensemble des opérations (d'une seule itération, les requêtes pourront être exécutée au moins 3x pour combler tous les trous) en un coup mais je désire avoir l'avis d'un expert avant de poursuivre mes recherches.

Voici la structure d'une table ressemblant à la mienne :

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
 
CREATE TABLE maTable (
'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
'time' int(10) unsigned NOT NULL,
'ref_cp' int(10) unsigned DEFAULT NULL,
'ref_cl' int(10) unsigned DEFAULT NULL,
'connb' int(10) unsigned DEFAULT NULL,
'ref_c' int(10) unsigned DEFAULT NULL,
'ibhr' varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
'ref_s' int(10) unsigned DEFAULT NULL,
PRIMARY KEY('id'),
KEY 'ref_c' ('ref_c'),
KEY 'ref_cl' ('ref_cl'),
KEY 'ibhr' ('ibhr'),
KEY 'ref_s' ('ref_s'),
KEY 'ref_cp' ('ref_cp')
) ENGINE=InnoDB

Et deux requêtes évoquant les recoupements à faire. Sont-elles correctes ? Cela sera-t-il plus efficace?

Code :

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

1
2
3
4
 
UPDATE maTable T1 JOIN maTable T2 ON T2.ibhr=T1.ibhr AND T2.time BETWEEN T1.time-10 AND T1.time+10 AND T2.ref_cl=T1.ref_cp SET T1.ref_c=T2.ref_c;
 
UPDATE maTable T1 JOIN maTable T2 ON T2.connb=T1.connb AND T1.ref_s=T2.ref_s SET T1.ref_c=T2.ref_c;

[CinePhil]

À première vue, syntaxiquement, ça me semble bon.

Par contre, il va y avoir deux problèmes :
1) Je crains que les requêtes mettent toutes les lignes à jour.
Il faudrait ajouter un WHERE T1.ref_c IS NULL
2) MySQL ne va pas aimer modifier une table en jointure avec elle-même.
Il va falloir passer par une table temporaire pour faire la jointure.

Sinon, oui, ce sera sans doute plus efficace en terme de temps d'exécution.

[fanfouer]

Bonjour Cinephil, merci pour cette réponse!

Par contre, il va y avoir deux problèmes :
1) Je crains que les requêtes mettent toutes les lignes à jour.
Il faudrait ajouter un WHERE T1.ref_c IS NULL

C'est intelligent!

On pourrait même rajouter un

Code :

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

1
2
 
AND T2.ref_c IS NOT NULL

2) MySQL ne va pas aimer modifier une table en jointure avec elle-même.
Il va falloir passer par une table temporaire pour faire la jointure.

Sinon, oui, ce sera sans doute plus efficace en terme de temps d'exécution.

Pour ça, j'ai eu l'occasion de commencer à tester sans les adaptations ci-dessus. C'est moyen.

Dès le lancement de la 1ere UPDATE, MySQL occupe 100% de mon CPU (4 coeurs) pendant plus d'une heure sans donner signe de vie.
J'ai arrêté l'exécution avant qu'elle arrive à son terme.
Donc MySQL doit accepter les jointures sur la table à mettre à jour par contre ca reste dantesque niveau ressources.

[CinePhil]

Ce qui doit plomber pas mal, c'est le critère de jointure sur un VARCHAR 150.

En plus, comme tu dois modifier 5 000 000 de lignes, peut-être qu'il juge que ce n'est pas la peine d'utiliser les index.Transforme l'UPDATE en SELECT et poste le résultat de EXPLAIN texte_de_la_requête STP.

Si effectivement il n'utilise pas les index, il serait judicieux de les désactiver (c'est à dire, si mes souvenirs sont bons, chez MySQL, de les supprimer) puis de faire l'UPDATE et de réactiver les index après les UPDATE.

[fanfouer]

Ce qui doit plomber pas mal, c'est le critère de jointure sur un VARCHAR 150.

Je suis entrain de me demander pourquoi VARCHAR(150), sachant que je n'ai que des entiers dans ma colonne.
Bon ca appelle quelques modifications de ce côté.

Transforme l'UPDATE en SELECT et poste le résultat de EXPLAIN texte_de_la_requête STP.

Le voici, avec un COUNT(*) également en dessous

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
 
mysql> EXPLAIN SELECT T1.ref_c FROM maTable T1 JOIN maTable T2 ON T2.ibhr=T1.ibhr AND T2.time BETWEEN T1.time-10 AND T1.time+10 AND T2.ref_cl=T1.ref_cp WHERE T1.ref_c IS NULL AND T2.ref_c IS NOT NULL;
+----+-------------+-------+------+--------------------------------------+---------------------------+---------+-----------------------+---------+-------------+
| id | select_type | table | type | possible_keys                        | key                       | key_len | ref                   | rows    | Extra       |
+----+-------------+-------+------+--------------------------------------+---------------------------+---------+-----------------------+---------+-------------+
|  1 | SIMPLE      | T2    | ALL  | ref_c,ref_cl,ibhr                    | NULL                      | NULL    | NULL                  | 5951444 | Using where |
|  1 | SIMPLE      | T1    | ref  | ref_c,ibhr,ref_cp                    | ref_cp                    | 5       | T2.ref_cl             |     467 | Using where |
+----+-------------+-------+------+--------------------------------------+---------------------------+---------+-----------------------+---------+-------------+
2 rows in set (0.10 sec)
 
mysql> SELECT COUNT(*) FROM maTable;
+----------+
| COUNT(*) |
+----------+
|  5713946 |
+----------+
1 row in set (3.49 sec)

[CinePhil]

C'est bien ce que je pensais, il n'utilise pas d'index sur T2.

Ceci dit, puisque, apparemment, tu souhaites mettre des infos quasiments identiques d'une ligne à l'autre, peut-être as-tu un problème de modélisation à la base ?

Qu'est-ce qui se cache derrière ces obscurs noms de colonnes ?

Que contient "maTable", elle aussi sans sémantique ?

[Fred_34]

Ne faut-il pas remplacer :

Code :

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

AND T2.time BETWEEN T1.time-10 AND T1.time+10

Par quelque chose du style : "le T2.time le plus proche de T1.time." ?

Ainsi tu n'aurais pas à lancer x fois la requête.

[fanfouer]

C'est bien ce que je pensais, il n'utilise pas d'index sur T2.

L'utilisation de ALTER TABLES maTable DISABLE KEYS serait donc pertinente?

Ceci dit, puisque, apparemment, tu souhaites mettre des infos quasiments identiques d'une ligne à l'autre, peut-être as-tu un problème de modélisation à la base ?

Qu'est-ce qui se cache derrière ces obscurs noms de colonnes ?

Que contient "maTable", elle aussi sans sémantique ?

J'ai peur de ne pas pouvoir en dire beaucoup plus malheureusement.
Les infos dont il est question semblent identiques mais il y a d'autres colonnes qui contiennent des infos bien différentes qui ne sont pas représentées dans ce topic.

Il faut prendre ma table comme un gros log d'événement qui sont générées par plusieurs acteurs.
Le processus dont il est question implique qu'à un moment ou un autre de la journée on perde la trace de l'acteur initiateur des événements. Le but de ma requête est de retrouver l'identifiant de l'acteur (ref_c) d'après un numéro de transition (ibhr).

Une fois les transitions complétées, on utilise la deuxième requête UPDATE pour compléter d'un trait des "séries" d'événements appartenant de fait au même acteur.

Je ne vois normalement pas de problèmes de modélisation et le jeu de données est tout de même "restreint"

Ne faut-il pas remplacer :

Code :

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

AND T2.time BETWEEN T1.time-10 AND T1.time+10

Par quelque chose du style : "le T2.time le plus proche de T1.time." ?

Ainsi tu n'aurais pas à lancer x fois la requête.

Tout est possible bien sur, à quelle fonction penses-tu en particulier?

[Fred_34]

Tout est possible bien sur, à quelle fonction penses-tu en particulier?

Il n'y a pas de fonction toute faite. J'essaie juste de comprendre ton besoin.
Pour l'instant, j'ai l'impression qu'au lieu de faire une jointure en cherchant -10 ou +10, tu devrais uniquement chercher l’élément précédent qui a les bonnes données.
Ce sera peut être moins performant ( ou pas), mais probablement correct ( actuellement tu peux avoir plusieurs enregistrements qui sont entre -10 et +10) et en tout cas tu lanceras une seule requête.

[fanfouer]

Pour l'instant, j'ai l'impression qu'au lieu de faire une jointure en cherchant -10 ou +10, tu devrais uniquement chercher l’élément précédent qui a les bonnes données.

C'est vrai.

Cependant je n'ai pas de données qui puisse me garantir l'unicité d'un élément précédent convenable.
L'identifiant de transition peut se répéter dans des intervalles de temps relativement courts et je ne peut pas connaitre exactement l'instant de l'événement précédant la transition et contenant l'identifiant de l'acteur.
C'est pourquoi je mets cette fourchette temporelle.

J'ai refais un test cet après-midi en modifiant donc le type du champ ibhr en INT(10), en ajoutant la clause WHERE et en désactivant les indexes sur maTable avec :

Code :

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

1
2
 
ALTER TABLE maTable DISABLE KEYS;

...mais le traitement reste long.
Environ 400k lignes ont été complétées en 1h. Il en reste 4 200 000.

Peut-être que la prochaine étape est bien la modification de la sélection temporelle.

Bon week end et merci pour votre aide.