Discussion :

[ben500fr]

Bonjour,

J'ai deux tables:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE T1 (
     the_time datetime(3),
     the_value float,
     PRIMARY KEY (the_time)
)
 
 
CREATE TABLE T2 (
     the_time datetime(3),
     the_value float,
     PRIMARY KEY (the_time)
)

Je souhaite faire une vue qui me donne the_value1 et the_value2 quand les dates sont identiques.
Petit détails: il y a entre 1 et 10 millions de lignes dans chaque table.

J'ai commence a faire une première vue comme ceci:

Code :

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

1
2
3
4
 
CREATE VIEW the_view AS
SELECT T1.the_time AS the_time, T1.the_value AS a, T2.the_value AS b FROM T1 INNER JOIN T2
ON T1.the_time = T2.the_time

Les performances sont très bonnes, mais j'ai besoin de faire la comparaison de date sans utiliser les millisecondes des colonnes the_time. Et la j'ai plus de mal!
J'ai essayé:

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE VIEW the_view AS
SELECT T1.the_time AS the_time, T1.the_value AS a, T2.the_value AS b FROM T1 INNER JOIN T2
ON YEAR(T1.the_time) = YEAR(T2.the_time)
AND MONTH(T1.the_time) = MONTH(T2.the_time)
AND DAY(T1.the_time) = DAY(T2.the_time)
AND HOUR(T1.the_time) = HOUR(T2.the_time)
AND MINUTE(T1.the_time) = MINUTE(T2.the_time)
AND SECOND(T1.the_time) = SECOND(T2.the_time)

Le résultat est bon, mais les performances sont catastrophique.
Avez vous une idée pour améliorer les choses?
Peut être créer une vue T1_bis qui copie T1 mais avec un datetime(0)?

Merci par avance!

[SQLpro]

1) une table devrait TOUJOURS avoir une clef primaire. Sans les performances se dégradent fortement. Ou sont vos clef primaires dans les deux tables ?
2) si les tantièmes de seconde ne vous intéressent pas, alors ne les stockez pas. Utilisez un DATETIME(0)
3) indexez vos données pour obtenir des performances
4) interdisez vous toute utilisation de fonction (en particulier dans votre cas YEAR, MONTH, DAY…) pour avoir des performances.

A +

[bstjean]

1) Peux-tu modifier les tables?
2) Il est clair que les multiples appels aux fonctions YEAR, MONTH, DAY, etc sont la cause de cette lenteur. Déjà, utiliser 1 seul appel avec DATE() (au lieux de YEAR, MONTH, DAY) pour comparer aiderait! Aussi, partitionner les données avec YEAR(thet_time) aiderait probablement !
3) Sur quelle version tu es?

[ben500fr]

1) une table devrait TOUJOURS avoir une clef primaire. Sans les performances se dégradent fortement. Ou sont vos clef primaires dans les deux tables ?

Erreur de ma part en rédigeant le post, les datetime(3) sont ma clé primaire. Je viens d’éditer le premier message.
Ça ne me viendrait pas a l'esprit de créer une table sans clé primaire, je vous rassure!
En plus je fais beaucoup de "LOAD DATA LOCAL INFILE..." en utilisant des sources redondantes: pas besoin de vérifier si les donnes sont déjà dans la base, MySQL le fait pour moi. C'est une des raison pour laquelle j'utilise MySQL et non des fichiers.
Actuellement j'ai 10To de data en format binaire dans des fichiers a déchiffrer et stocker dans des bases... J’espère n'en sauver que 500Go après avoir fait le tri!

2) si les tantièmes de seconde ne vous intéressent pas, alors ne les stockez pas. Utilisez un DATETIME(0)

Malheureusement dans certains cas les millisecondes sont très importantes pour moi.
En fait je stocke toutes les telemetries de plusieurs satellites, je ne dois pas alterer les data.

3) indexez vos données pour obtenir des performances

Avec une cle primaire sur la colonne "the_time" je ne pense pas avoir besoin d'index ou je fais erreur?

4) interdisez vous toute utilisation de fonction (en particulier dans votre cas YEAR, MONTH, DAY…) pour avoir des performances.

J'en prend note

1) Peux-tu modifier les tables?

Oui a volonté si je ne porte pas atteinte a l’intégrité des données sources.

2) Il est clair que les multiples appels aux fonctions YEAR, MONTH, DAY, etc sont la cause de cette lenteur. Déjà, utiliser 1 seul appel avec DATE() (au lieux de YEAR, MONTH, DAY) pour comparer aiderait! Aussi, partitionner les données avec YEAR(thet_time) aiderait probablement !

Partitionner les donnes... dans quel sens? Un table par année?

3) Sur quelle version tu es?

MariaDB 5.5.60 sur une Reh Hat Enterprise Linux 7.6

[ben500fr]

Pour tout de suite j'ai cette solution:

Code :

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

1
2
3
4
5
6
 
CREATE VIEW the_view AS
SELECT T1.the_time AS the_time, T1.the_value AS a, T2.the_value AS b FROM T1 INNER JOIN T2
ON T1.the_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
AND T1.the_time <= T2.the_time
AND T1.the_time >= DATE_SUB(T2.the_time, INTERVAL 1 SECOND)

inconvénient : Je m'impose de n'utiliser les data que des dernière 24 heures (a voir en fonction des performances...)
La requête prend 19 secondes a tourner...


Question subsidiaire, attention ça va peut être vous choquer : est il possible de forcer le serveur a "calculer" la vue toute les 30 minutes par exemple, et du coup de faire des "SELECT * FROM ma_vue" tres rapide?
J'ai bien peur que ça aille a l'inverse de principe des vues, mais je le tente!

[bstjean]

Pour mieux pouvoir répondre à tes questions, il faudrait savoir quel genre de requêtes tu fais (des exemples réels) ainsi qu'une idée de la répartition des données. Aussi, il faudrait savoir ce que tu essaie de faire (expliqué en MOTS) !

[escartefigue]

Avec une cle primaire sur la colonne "the_time" je ne pense pas avoir besoin d'index ou je fais erreur?

L'indexation de la clef primaire est automatique pour MySQL

4) interdisez vous toute utilisation de fonction (en particulier dans votre cas YEAR, MONTH, DAY…) pour avoir des performances.

J'en prend note

Plus précisément, dans votre requête vous utilisez des fonctions de chaque coté des critères de jointure, c'est ce qu'il ne faut pas faire car cela rende votre requête non "SArgAble" (non indexable)
Par contre, on peut utiliser des fonctions sur l'une des deux tables en jointure : soit deux tables T1 et T2, si ma jointure recherche les lignes dans T2 en correspondance avec les valeurs déjà connues de T1, il faut mettre les fonctions sur les colonnes de T1, pas sur celles de T2

Partitionner les donnes... dans quel sens? Un table par année?

Quand on parle de partitionnement on pense en général au partitionnement horizontal. Il s'agit, dans une seule et même table, de répartir les lignes dans plusieurs espaces de stockage, plusieurs tablespace donc, en fonction d'un critère de partitionnement. Cette technique est particulièrement recommandée pour les tables à très forte volumétrie.
Le partitionnement vertical, lui, est utile dans le cas où des colonnes sont très volumineuses, on crée une deuxième table, dont la PK est la même que la table principale et dans laquelle on déporte ces colonnes.

[Artemus24]

Salut ben500fr.

Petit détails: il y a entre 1 et 10 millions de lignes dans chaque table.

Un problème de performance !

Je souhaite faire une vue qui me donne the_value1 et the_value2 quand les dates sont identiques.

D'accord.

mais j'ai besoin de faire la comparaison de date sans utiliser les millisecondes des colonnes the_time.

Ce n'est pas possible, si vous désirez la performance.

Il aurait fallu y penser avant de créer des tables qui ne sont pas conformes à ce que vous essayez de faire.

Mais si on les modifie, voici ce que vous pouvez faire :

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `t1`
--------------
 
--------------
CREATE TABLE `t1`
( `the_time`      datetime(3) NOT NULL primary key,
  `the_value`     float       NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t1` (`the_time`,`the_value`) values
  ('2018-12-21 05:44:33.123',125.44),
  ('2018-12-21 05:44:33.456',251.21),
  ('2018-12-21 05:45:17.005',452.12),
  ('2018-12-22 05:45:49.751',128.33),
  ('2018-12-22 05:46:23.789',351.72)
--------------
 
--------------
select * from `t1`
--------------
 
+-------------------------+-----------+
| the_time                | the_value |
+-------------------------+-----------+
| 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:45:17.005 |    452.12 |
| 2018-12-22 05:45:49.751 |    128.33 |
| 2018-12-22 05:46:23.789 |    351.72 |
+-------------------------+-----------+
--------------
DROP TABLE IF EXISTS `t2`
--------------
 
--------------
CREATE TABLE `t2`
( `the_time`      datetime(3) NOT NULL primary key,
  `the_value`     float       NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t2` (`the_time`,`the_value`) values
  ('2018-12-21 05:44:33.123',125.44),
  ('2018-12-21 05:44:33.456',251.21),
  ('2018-12-21 05:45:17.005',452.12),
  ('2018-12-22 05:45:49.751',128.33),
  ('2018-12-22 05:46:23.789',351.72)
--------------
 
--------------
select * from `t2`
--------------
 
+-------------------------+-----------+
| the_time                | the_value |
+-------------------------+-----------+
| 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:45:17.005 |    452.12 |
| 2018-12-22 05:45:49.751 |    128.33 |
| 2018-12-22 05:46:23.789 |    351.72 |
+-------------------------+-----------+
--------------
commit
--------------
 
--------------
alter table `t1` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
--------------
 
--------------
alter table `t1` add index `idx1` (the_time_bis)
--------------
 
--------------
describe `t1`
--------------
 
+--------------+-------------+------+-----+---------+------------------+
| Field        | Type        | Null | Key | Default | Extra            |
+--------------+-------------+------+-----+---------+------------------+
| the_time     | datetime(3) | NO   | PRI | NULL    |                  |
| the_value    | float       | NO   |     | NULL    |                  |
| the_time_bis | datetime    | NO   | MUL | NULL    | STORED GENERATED |
+--------------+-------------+------+-----+---------+------------------+
--------------
select * from `t1`
--------------
 
+-------------------------+-----------+---------------------+
| the_time                | the_value | the_time_bis        |
+-------------------------+-----------+---------------------+
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33 |
| 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17 |
| 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:50 |
| 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:24 |
+-------------------------+-----------+---------------------+
--------------
commit
--------------
 
--------------
alter table `t2` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
--------------
 
--------------
alter table `t2` add index `idx2` (the_time_bis)
--------------
 
--------------
describe `t2`
--------------
 
+--------------+-------------+------+-----+---------+------------------+
| Field        | Type        | Null | Key | Default | Extra            |
+--------------+-------------+------+-----+---------+------------------+
| the_time     | datetime(3) | NO   | PRI | NULL    |                  |
| the_value    | float       | NO   |     | NULL    |                  |
| the_time_bis | datetime    | NO   | MUL | NULL    | STORED GENERATED |
+--------------+-------------+------+-----+---------+------------------+
--------------
select * from `t2`
--------------
 
+-------------------------+-----------+---------------------+
| the_time                | the_value | the_time_bis        |
+-------------------------+-----------+---------------------+
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33 |
| 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17 |
| 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:50 |
| 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:24 |
+-------------------------+-----------+---------------------+
--------------
commit
--------------
 
--------------
select      t1.the_time,
            t1.the_value,
            t2.the_time,
            t2.the_value
 
     from  `t1` as t1
 
inner join `t2` as t2
        on  t2.the_time = t1.the_time
--------------
 
+-------------------------+-----------+-------------------------+-----------+
| the_time                | the_value | the_time                | the_value |
+-------------------------+-----------+-------------------------+-----------+
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17.005 |    452.12 |
| 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:49.751 |    128.33 |
| 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:23.789 |    351.72 |
+-------------------------+-----------+-------------------------+-----------+
--------------
explain
select      t1.the_time,
            t1.the_value,
            t2.the_time,
            t2.the_value
 
     from  `t1` as t1
 
inner join `t2` as t2
        on  t2.the_time = t1.the_time
--------------
 
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL             |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 7       | base.t2.the_time |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
--------------
select      t1.the_time,
            t1.the_value,
            t2.the_time,
            t2.the_value
 
     from  `t1` as t1
 
inner join `t2` as t2
        on  t2.the_time_bis = t1.the_time_bis
--------------
 
+-------------------------+-----------+-------------------------+-----------+
| the_time                | the_value | the_time                | the_value |
+-------------------------+-----------+-------------------------+-----------+
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17.005 |    452.12 |
| 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:49.751 |    128.33 |
| 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:23.789 |    351.72 |
+-------------------------+-----------+-------------------------+-----------+
--------------
explain
select      t1.the_time,
            t1.the_value,
            t2.the_time,
            t2.the_value
 
     from  `t1` as t1
 
inner join `t2` as t2
        on  t2.the_time_bis = t1.the_time_bis
--------------
 
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx2          | NULL | NULL    | NULL                 |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx1          | idx1 | 5       | base.t2.the_time_bis |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

[ben500fr]

Pour mieux pouvoir répondre à tes questions, il faudrait savoir quel genre de requêtes tu fais (des exemples réels) ainsi qu'une idée de la répartition des données. Aussi, il faudrait savoir ce que tu essaie de faire (expliqué en MOTS) !

Je souahite avoir une interface utilisateur qui affiche des graphs / plots de données satellites. Ma question originelle est ici:
https://www.developpez.net/forums/d1...s-issus-mysql/

J'ai, pour chaque paramètre, sa dates (a la milliseconde près) et sa valeur, un FLOAT.
Pour les stocker, j'ai choisi de faire un database par satellite et une table par paramètre.

Je crée l'interface en php, et je dessine mes graphs avec Gnuplot, qui me semble super performant, même avec 1.000.000 couples date/valeur en entrée.


Dans le cas particulier de ce topic, je souhaite faire un graph avec un paramètre sur l'axe x, et un paramètre sur l'axe y. D’où ma jointure.
Le plus important est que le graph s'affiche vite pour l'utilisateur (temps requete SQL + generation PHP + création du graphe avec gnuplot).
Bien sure, je ne reçois pas forcement les paramètres de l'axe x et de l'axe y au même moment. D’où ma demande de jointure avec des datetime sans millisecondes, et même a 5 secondes près.
A terme pour les graphes avec un paramètre sur x au lieu du temps, je pense que je proposerai a l'utilisateur de choisir la "qualité" de la jointure: a la milliseconde, la seconde, 5 secondes ou 15 secondes.

Voile j’espère que c'est plus claire

Il aurait fallu y penser avant de créer des tables qui ne sont pas conformes à ce que vous essayez de faire.

Mais si on les modifie, voici ce que vous pouvez faire :

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `t1`
--------------
 
--------------
CREATE TABLE `t1`
( `the_time`      datetime(3) NOT NULL primary key,
  `the_value`     float       NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t1` (`the_time`,`the_value`) values
  ('2018-12-21 05:44:33.123',125.44),
  ('2018-12-21 05:44:33.456',251.21),
  ('2018-12-21 05:45:17.005',452.12),
  ('2018-12-22 05:45:49.751',128.33),
  ('2018-12-22 05:46:23.789',351.72)
--------------
 
--------------
select * from `t1`
--------------
 
+-------------------------+-----------+
| the_time                | the_value |
+-------------------------+-----------+
| 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:45:17.005 |    452.12 |
| 2018-12-22 05:45:49.751 |    128.33 |
| 2018-12-22 05:46:23.789 |    351.72 |
+-------------------------+-----------+
--------------
DROP TABLE IF EXISTS `t2`
--------------
 
--------------
CREATE TABLE `t2`
( `the_time`      datetime(3) NOT NULL primary key,
  `the_value`     float       NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t2` (`the_time`,`the_value`) values
  ('2018-12-21 05:44:33.123',125.44),
  ('2018-12-21 05:44:33.456',251.21),
  ('2018-12-21 05:45:17.005',452.12),
  ('2018-12-22 05:45:49.751',128.33),
  ('2018-12-22 05:46:23.789',351.72)
--------------
 
--------------
select * from `t2`
--------------
 
+-------------------------+-----------+
| the_time                | the_value |
+-------------------------+-----------+
| 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:45:17.005 |    452.12 |
| 2018-12-22 05:45:49.751 |    128.33 |
| 2018-12-22 05:46:23.789 |    351.72 |
+-------------------------+-----------+
--------------
commit
--------------
 
--------------
alter table `t1` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
--------------
 
--------------
alter table `t1` add index `idx1` (the_time_bis)
--------------
 
--------------
describe `t1`
--------------
 
+--------------+-------------+------+-----+---------+------------------+
| Field        | Type        | Null | Key | Default | Extra            |
+--------------+-------------+------+-----+---------+------------------+
| the_time     | datetime(3) | NO   | PRI | NULL    |                  |
| the_value    | float       | NO   |     | NULL    |                  |
| the_time_bis | datetime    | NO   | MUL | NULL    | STORED GENERATED |
+--------------+-------------+------+-----+---------+------------------+
--------------
select * from `t1`
--------------
 
+-------------------------+-----------+---------------------+
| the_time                | the_value | the_time_bis        |
+-------------------------+-----------+---------------------+
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33 |
| 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17 |
| 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:50 |
| 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:24 |
+-------------------------+-----------+---------------------+
--------------
commit
--------------
 
--------------
alter table `t2` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL
--------------
 
--------------
alter table `t2` add index `idx2` (the_time_bis)
--------------
 
--------------
describe `t2`
--------------
 
+--------------+-------------+------+-----+---------+------------------+
| Field        | Type        | Null | Key | Default | Extra            |
+--------------+-------------+------+-----+---------+------------------+
| the_time     | datetime(3) | NO   | PRI | NULL    |                  |
| the_value    | float       | NO   |     | NULL    |                  |
| the_time_bis | datetime    | NO   | MUL | NULL    | STORED GENERATED |
+--------------+-------------+------+-----+---------+------------------+
--------------
select * from `t2`
--------------
 
+-------------------------+-----------+---------------------+
| the_time                | the_value | the_time_bis        |
+-------------------------+-----------+---------------------+
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33 |
| 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17 |
| 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:50 |
| 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:24 |
+-------------------------+-----------+---------------------+
--------------
commit
--------------
 
--------------
select      t1.the_time,
            t1.the_value,
            t2.the_time,
            t2.the_value
 
     from  `t1` as t1
 
inner join `t2` as t2
        on  t2.the_time = t1.the_time
--------------
 
+-------------------------+-----------+-------------------------+-----------+
| the_time                | the_value | the_time                | the_value |
+-------------------------+-----------+-------------------------+-----------+
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17.005 |    452.12 |
| 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:49.751 |    128.33 |
| 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:23.789 |    351.72 |
+-------------------------+-----------+-------------------------+-----------+
--------------
explain
select      t1.the_time,
            t1.the_value,
            t2.the_time,
            t2.the_value
 
     from  `t1` as t1
 
inner join `t2` as t2
        on  t2.the_time = t1.the_time
--------------
 
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL             |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 7       | base.t2.the_time |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
--------------
select      t1.the_time,
            t1.the_value,
            t2.the_time,
            t2.the_value
 
     from  `t1` as t1
 
inner join `t2` as t2
        on  t2.the_time_bis = t1.the_time_bis
--------------
 
+-------------------------+-----------+-------------------------+-----------+
| the_time                | the_value | the_time                | the_value |
+-------------------------+-----------+-------------------------+-----------+
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.123 |    125.44 |
| 2018-12-21 05:44:33.123 |    125.44 | 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:44:33.456 |    251.21 | 2018-12-21 05:44:33.456 |    251.21 |
| 2018-12-21 05:45:17.005 |    452.12 | 2018-12-21 05:45:17.005 |    452.12 |
| 2018-12-22 05:45:49.751 |    128.33 | 2018-12-22 05:45:49.751 |    128.33 |
| 2018-12-22 05:46:23.789 |    351.72 | 2018-12-22 05:46:23.789 |    351.72 |
+-------------------------+-----------+-------------------------+-----------+
--------------
explain
select      t1.the_time,
            t1.the_value,
            t2.the_time,
            t2.the_value
 
     from  `t1` as t1
 
inner join `t2` as t2
        on  t2.the_time_bis = t1.the_time_bis
--------------
 
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx2          | NULL | NULL    | NULL                 |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx1          | idx1 | 5       | base.t2.the_time_bis |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+------+----------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Cette modification des tables semble être la solution idéale dans mon cas: l’écriture dans la base sera un peu plus lente, la bases prendront un peu (beaucoup?) plus de place dans les disques, mais les requêtes se feront BEAUCOUP plus vite

Premier point: chez moi

Code :

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

alter table `t1` add `the_time_bis`  datetime(0) generated always as (`the_time`) stored NOT NULL

produit une erreur "1064" a cause du "stored" et du "NOT NULL".
Je remplace le "STORED" par "PERSISTENT", je ne suis pas sure de la différence entre les deux. Et je rajouter l’attribut "NOT NULL" avec une seconde requête SQL.

Second point: Si au lieu de sauvegarder les dates a seconde près, je souhaite les sauvegarder a 5 secondes près par exemple? Je vais creer une colonne avec ces dates arrondi, je vois plusieurs solutions sur les forums:

Code :

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

1
2
from_unixtime(floor(unix_timestamp('2006-10-10 14:26:01')/(60*5))*(60*5))
FROM_UNIXTIME(UNIX_TIMESTAMP(time) - MOD(UNIX_TIMESTAMP(time),300))

Vous auriez un conseil a me donner?

Dernier point: quand je fais ma jointure

Code :

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

1
2
3
4
5
6
 
create view the_view AS
select      t1.the_time, t1.the_value, t2.the_time, t2.the_value
from  `t1` as t1
inner join `t2` as t2
on  t2.the_time = t1.the_time

je me retrouve avec la colonne t1.the_time non unique. Normale me direz vous au vue de la requête.
Dois-je modifier la requetes pour avoir t1.the_time unique (une seule ligne)?
Ou il faut simplement declarer le champ t1.the_time comme cle primaire de la vue?

[Artemus24]

Salut ben500fr.

Vous auriez un conseil à me donner?

Vous avez plusieurs contraintes sur ce que vous cherchez à faire :

1) ne pas trop augmenter la volumétrie de vos tables.
2) ne pas trop ralentir l'écriture dans vos tables.
3) obtenir la précision que vous cherchez.
4) faire des jointures afin de récupérer les paramètres X et Y.
5) vous ne recevez pas ces paramètres au même moment.
6) au final, obtenir le couple (X ; Y).
7) optimiser la requête d'extraction pour obtenir le couple val(X ; Y).

je ne reçois pas forcement les paramètres de l'axe x et de l'axe y au même moment.

Le problème se trouve là. D'où l'impossibilité de faire correctement vos jointures.

D’où ma demande de jointure avec des datetime sans millisecondes, et même a 5 secondes près.

La précision est secondaire quand on a le couple val(X ; Y).

Je souhaite faire une vue qui me donne the_value1 et the_value2 quand les dates sont identiques.

J'espère que vous n'avez pas que la date comme moyen de faire le rapprochement entre votre X et votre Y.
Vous dites que vous ne les recevez pas en même temps. Mais pourtant, vous faites le rapprochement sur la date (timestamp).
Vous dites un peu plus loin, que le rapprochement doit se faire sans les millisecondes, voire même à cinq secondes près.
Vous aurez inévitablement des doublons. Comment alors faire le rapprochement ?

La date ne sert qu'à trouver le couple val(X ; Y), mais pas à faire le rapprochement entre le X et le Y.

Dois-je comprendre que vous avez en fait deux flux, l'un pour le X et l'autre pour le Y.
Le premier X n'ayant pas de Y, recevra automatiquement le prochain Y, et vice-versa.
Avez-vous besoin de conserver vos deux tables, l'une pour le X et l'autre pour le Y ?
Je trouve qu'ils ne sont pas utilise et pose le problème du rapprochement.

La mécanique à mettre en place consiste d'abord à identifier le flux X ou Y en premier.
Si la ligne n'existe pas alors la créer.
Si la ligne existe déjà, venir mettre à jour la partie Y si la partie est déjà renseignée, et vice-versa.
Les attributions vont se faire dans l'ordre d'arrivée, à savoir le premier X avec le premier Y, le deuxième X avec le deuxième Y, et ainsi de suite.
Voici ce que je propose :

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `coordonnee`
--------------
 
--------------
CREATE TABLE `coordonnee`
( `id`       integer unsigned  NOT NULL auto_increment primary key,
  `val_X`    float                 NULL,
  `temps_X`  datetime(3)           NULL,
  `val_Y`    float                 NULL,
  `temps_Y`  datetime(3)           NULL,
  index `idx1` (`val_x`,`val_y`,`id`),
  index `idx2` (`val_y`,`val_x`,`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`    integer unsigned  NOT NULL auto_increment primary key,
  `code`  char(01)          NOT NULL,
  `val`   float             NOT NULL,
  `temps` datetime(3)       NOT NULL,
  index `idx` (`code`,`temps`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `dispatch`
--------------
 
--------------
CREATE TRIGGER `dispatch`
BEFORE insert ON `test`
FOR EACH ROW
BEGIN
  DECLARE _id  integer DEFAULT NULL;
 
  IF NEW.code = 'X' THEN
     select min(id) into _id from `coordonnee` where val_x is null and val_y is not null;
  ELSE
     select min(id) into _id from `coordonnee` where val_y is null and val_x is not null;
  END IF;
 
  IF _id is not null then
     IF NEW.code = 'X' THEN
        update `coordonnee` set val_x = NEW.val, temps_x = NEW.temps where id = _id;
     ELSE
        update `coordonnee` set val_y = NEW.val, temps_y = NEW.temps where id = _id;
     END IF;
  ELSE
     IF NEW.code = 'X' THEN
        insert into `coordonnee` (`val_x`,`temps_x`) values (NEW.val, NEW.temps);
     ELSE
        insert into `coordonnee` (`val_y`,`temps_y`) values (NEW.val, NEW.temps);
     END IF;
  END IF;
END
--------------
 
--------------
insert into `test` (`code`,`val`,`temps`) values
  ('X', 17.12, '2019-02-12 10:00:00.123'),
  ('X', 16.23, '2019-02-12 10:00:01.123'),
  ('Y', 15.34, '2019-02-12 10:00:02.123'),
  ('X', 14.45, '2019-02-12 10:00:03.123'),
  ('X', 13.56, '2019-02-12 10:00:04.123'),
  ('Y', 12.67, '2019-02-12 10:00:05.123'),
  ('Y', 11.78, '2019-02-12 10:00:06.123'),
  ('Y', 10.89, '2019-02-12 10:00:07.123'),
  ('Y', 18.90, '2019-02-12 10:00:08.123'),
  ('X', 19.01, '2019-02-12 10:00:09.123')
--------------
 
--------------
select * from `test`
--------------
 
+----+------+-------+-------------------------+
| id | code | val   | temps                   |
+----+------+-------+-------------------------+
|  1 | X    | 17.12 | 2019-02-12 10:00:00.123 |
|  2 | X    | 16.23 | 2019-02-12 10:00:01.123 |
|  3 | Y    | 15.34 | 2019-02-12 10:00:02.123 |
|  4 | X    | 14.45 | 2019-02-12 10:00:03.123 |
|  5 | X    | 13.56 | 2019-02-12 10:00:04.123 |
|  6 | Y    | 12.67 | 2019-02-12 10:00:05.123 |
|  7 | Y    | 11.78 | 2019-02-12 10:00:06.123 |
|  8 | Y    | 10.89 | 2019-02-12 10:00:07.123 |
|  9 | Y    |  18.9 | 2019-02-12 10:00:08.123 |
| 10 | X    | 19.01 | 2019-02-12 10:00:09.123 |
+----+------+-------+-------------------------+
--------------
select * from `coordonnee`
--------------
 
+----+-------+-------------------------+-------+-------------------------+
| id | val_X | temps_X                 | val_Y | temps_Y                 |
+----+-------+-------------------------+-------+-------------------------+
|  1 | 17.12 | 2019-02-12 10:00:00.123 | 15.34 | 2019-02-12 10:00:02.123 |
|  2 | 16.23 | 2019-02-12 10:00:01.123 | 12.67 | 2019-02-12 10:00:05.123 |
|  3 | 14.45 | 2019-02-12 10:00:03.123 | 11.78 | 2019-02-12 10:00:06.123 |
|  4 | 13.56 | 2019-02-12 10:00:04.123 | 10.89 | 2019-02-12 10:00:07.123 |
|  5 | 19.01 | 2019-02-12 10:00:09.123 |  18.9 | 2019-02-12 10:00:08.123 |
+----+-------+-------------------------+-------+-------------------------+
--------------
explain select min(id) from `coordonnee` where val_x is null and val_y is not null
--------------
 
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | coordonnee | NULL       | range | idx1,idx2     | idx1 | 10      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
--------------
explain select min(id) from `coordonnee` where val_y is null and val_x is not null
--------------
 
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | coordonnee | NULL       | range | idx1,idx2     | idx2 | 10      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Afin de savoir si le rapprochement prend énormément de temps, il faudra faire des tests de performances.

@+

[ben500fr]

J'espère que vous n'avez pas que la date comme moyen de faire le rapprochement entre votre X et votre Y.
Vous dites que vous ne les recevez pas en même temps. Mais pourtant, vous faites le rapprochement sur la date (timestamp).
Vous dites un peu plus loin, que le rapprochement doit se faire sans les millisecondes, voire même à cinq secondes près.
Vous aurez inévitablement des doublons. Comment alors faire le rapprochement ?

merci infiniment pour votre temps et vos réponses.
Je suis en train de me rendre compte du gouffre entre un utilisateur SQL occasionnelle et un pro...

Oui je ne fais le rapprochement que en fonction du temps. Je vous explique:
Les satellites envoient chaque paramètre a un débit différents, de 10 fois par secondes a une fois toutes les 10 minutes.
Un des cas qui m’intéresse est l’analyse du roulement a bille sur une roue a inertie (un moteur qui fait tourner une masse: ça crée de la stabilité et permet de pivoter sur un axe).
J'ai besoin de tracer la fiction de l'axe en fonction de la vitesse, voici deux images trouvées sur google qui donne "en gros" la théorie et le résultat souhaite:



Sachant que la vitesse évolue très lentement, on peut considérer que le vitesse reçu est valable quelques secondes (voir quelques minutes).
Une autre solution serait même de ne choisir qu'un seul couple de valeur toutes les minutes, disons la valeur MAX sur cette période. Mais je ne suis pas fan.

Bref, pour faire ce tracé, a chaque fois que je reçois une indication de vitesse:

• si je reçois une indication de friction dans les 5 secondes qui suivent, alors je veux garder le couple (vitesse,friction).
• si je reçois deux fois la friction dans les 5 secondes suivantes, alors je ne veux garder qu'un seul couple (vitesse,friction).
• si je ne reçois pas d'indication de friction dans les 5 secondes suivantes, alors je ne sauvegarde aucune valeur.

Donc avec le paramétrage de "5 secondes" j'aurai un certain nombre de couples (vitesse,friction).
Si je veux plus de couples (vitesse,friction), donc plus de points sur mon graphe, alors je peux changer le paramétrage a "15 secondes" par exemple.
Si j'ai trop de points (c'est très subjectif), alors je peux descendre le paramétrage a "1 seconde"


Le couple (vitesse,friction) marche très bien avec "5 secondes", mais sur d'autres paramètres il me faudra peut être faire une jointure a la seconde près, voir la milliseconde près si ce sont des paramètres reçu dans le même paquet.
D’où mon importance de pouvoir laisser l'utilisateur choisir.

C'est pour ca que j'aime beaucoup la solution de creer des colonnes "the_time_bis" en datetime(0) pour la seconde pres, et aussi uen colonne "the_time_ter" qui contient la date arrondi a 5 secondes, et une dernier colonne a 15 ou 30 secondes.

[Artemus24]

Salut ben500fr.

Cela devient compliqué.

Les satellites envoient chaque paramètre a un débit différents, de 10 fois par secondes a une fois toutes les 10 minutes.

Vous avez un flux pour chaque satellite avec une périodicité différente dans les émissions des données.
Vous avez besoin de récupérer le couple (vitesse ; friction) sachant que la vitesse évolue lentement tandis que la friction évolue très rapidement.

Ensuite, un utilisateur demande une périodicité qui va lui servir à faire un graphe.
Sa demande est basée sur une périodicité de 5 secondes, mais elle peut être autre, au choix de l'utilisateur.

Bref, pour faire ce tracé, à chaque fois que je reçois une indication de vitesse:
si je reçois une indication de friction dans les 5 secondes qui suivent, alors je veux garder le couple (vitesse,friction).
si je reçois deux fois la friction dans les 5 secondes suivantes, alors je ne veux garder qu'un seul couple (vitesse,friction).
si je ne reçois pas d'indication de friction dans les 5 secondes suivantes, alors je ne sauvegarde aucune valeur.

La solution est facile à mettre en oeuvre. D'ailleurs vous la donnez en disant que vous devez diviser le temps unix par 5 secondes.
Autrement dit l'intervalle de temps sera x borne inférieur comprise et x+5 borne supérieure non comprise.
Et vous recherchez une vitesse et une friction dans cet intervalle de temps.
La première valeur de vitesse et la première valeur de friction correspondant à votre intervalle seront candidats à la sélection.
Si l'une des deux est absente, aucune sélection.

Donc avec le paramétrage de "5 secondes" j'aurai un certain nombre de couples (vitesse,friction).

Un couple par intervalle de 5 secondes, si le couple existe. Il faut aussi envisager des trous.

Si je veux plus de couples (vitesse,friction), donc plus de points sur mon graphe, alors je peux changer le paramétrage a "15 secondes" par exemple.

Non, c'est l'inverse. Si vous avez un couple toutes les cinq secondes, si vous en voulez disons cinq fois plus, l'intervalle sera de 1 seconde.
En passant à 15 secondes vis-à-vis des 5 secondes, vous aurez trois fois moins de couple.

Si j'ai trop de points (c'est très subjectif), alors je peux descendre le paramétrage a "1 seconde"

Et bien non, vous l'augmentez.

Si vous avez un couple par intervalle est de 5 secondes, en 1 heure vous en avez 720 couples.
Si maintenant, l'intervalle est de 15 secondes, en 1 heure vous passez à 240 couples, soit 240 / 720 = 1/3, soit trois fois moins.
Si l'intervalle passe à 1 seconde, en 1 heure vous en avez 3600, soit 3600 / 720 = 5, soit cinq fois plus.

D’où mon importance de pouvoir laisser l'utilisateur choisir.

D'où le problème de la performance pour créer vos couples dans la périodicité demandé.

C'est pour ca que j'aime beaucoup la solution de creer des colonnes "the_time_bis" en datetime(0) pour la seconde pres, et aussi uen colonne "the_time_ter" qui contient la date arrondi a 5 secondes, et une dernier colonne a 15 ou 30 secondes.

Et bien non, car la solution que je vous ai donnée était basée sur une périodicité fixée à l'avance.
Là, c'est différent car vous demandez différentes périodicité, au choix de l'utilisateur.

Voilà ce que je propose :

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `t1`
--------------
 
--------------
CREATE TABLE `t1`
( `the_time`   decimal(21,3)  NOT NULL primary key,
  `the_value`  float          NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t1` (`the_time`,`the_value`) values
  (unix_timestamp('2019-02-12 10:20:20.123'),100.44),
  (unix_timestamp('2019-02-12 10:20:21.123'),105.44),
  (unix_timestamp('2019-02-12 10:20:22.123'),110.44),
  (unix_timestamp('2019-02-12 10:20:23.123'),115.44),
  (unix_timestamp('2019-02-12 10:20:24.123'),120.44),
  (unix_timestamp('2019-02-12 10:20:25.123'),125.44),
  (unix_timestamp('2019-02-12 10:20:26.123'),130.44),
  (unix_timestamp('2019-02-12 10:20:27.123'),135.44),
  (unix_timestamp('2019-02-12 10:20:28.123'),140.44),
  (unix_timestamp('2019-02-12 10:20:29.123'),145.44),
  (unix_timestamp('2019-02-12 10:20:30.123'),150.44),
  (unix_timestamp('2019-02-12 10:20:31.123'),155.44),
  (unix_timestamp('2019-02-12 10:20:32.123'),160.44),
  (unix_timestamp('2019-02-12 10:20:33.123'),165.44),
  (unix_timestamp('2019-02-12 10:20:34.123'),170.44),
  (unix_timestamp('2019-02-12 10:20:35.123'),175.44),
  (unix_timestamp('2019-02-12 10:20:36.123'),180.44),
  (unix_timestamp('2019-02-12 10:20:37.123'),185.44),
  (unix_timestamp('2019-02-12 10:20:38.123'),190.44),
  (unix_timestamp('2019-02-12 10:20:39.123'),195.44)
--------------
 
--------------
select * from `t1`
--------------
 
+----------------+-----------+
| the_time       | the_value |
+----------------+-----------+
| 1549963220.123 |    100.44 |
| 1549963221.123 |    105.44 |
| 1549963222.123 |    110.44 |
| 1549963223.123 |    115.44 |
| 1549963224.123 |    120.44 |
| 1549963225.123 |    125.44 |
| 1549963226.123 |    130.44 |
| 1549963227.123 |    135.44 |
| 1549963228.123 |    140.44 |
| 1549963229.123 |    145.44 |
| 1549963230.123 |    150.44 |
| 1549963231.123 |    155.44 |
| 1549963232.123 |    160.44 |
| 1549963233.123 |    165.44 |
| 1549963234.123 |    170.44 |
| 1549963235.123 |    175.44 |
| 1549963236.123 |    180.44 |
| 1549963237.123 |    185.44 |
| 1549963238.123 |    190.44 |
| 1549963239.123 |    195.44 |
+----------------+-----------+
--------------
DROP TABLE IF EXISTS `t2`
--------------
 
--------------
CREATE TABLE `t2`
( `the_time`   decimal(21,3)  NOT NULL primary key,
  `the_value`     float       NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t2` (`the_time`,`the_value`) values
  (unix_timestamp('2019-02-12 10:20:16.456'),200.44),
  (unix_timestamp('2019-02-12 10:20:21.456'),210.44),
  (unix_timestamp('2019-02-12 10:20:26.456'),220.44),
  (unix_timestamp('2019-02-12 10:20:31.456'),230.44),
  (unix_timestamp('2019-02-12 10:20:36.456'),240.44),
  (unix_timestamp('2019-02-12 10:20:41.456'),250.44)
--------------
 
--------------
select * from `t2`
--------------
 
+----------------+-----------+
| the_time       | the_value |
+----------------+-----------+
| 1549963216.456 |    200.44 |
| 1549963221.456 |    210.44 |
| 1549963226.456 |    220.44 |
| 1549963231.456 |    230.44 |
| 1549963236.456 |    240.44 |
| 1549963241.456 |    250.44 |
+----------------+-----------+
--------------
commit
--------------
 
--------------
drop procedure if exists `trait`
--------------
 
--------------
create procedure `trait` (IN  _periode smallint)
DETERMINISTIC
NO SQL
BEGIN
  drop temporary table if exists `temp1`;
 
  SET @req1 = concat('create temporary table `temp1` ( unique index (`periode`) ) ENGINE=InnoDB as ( select  cast((the_time / ', _periode, ') as unsigned) * ', _periode, ' as periode, min(the_value) as the_value from `t1` group by periode);');
 
  PREPARE            stmt FROM @req1;
  EXECUTE            stmt;
  DEALLOCATE PREPARE stmt;
 
  SET @req2 = concat('create temporary table `temp2` ( unique index (`periode`) ) ENGINE=InnoDB as ( select  cast((the_time / ', _periode, ') as unsigned) * ', _periode, ' as periode, min(the_value) as the_value from `t2` group by periode);');
 
  drop temporary table if exists `temp2`;
 
  PREPARE            stmt FROM @req2;
  EXECUTE            stmt;
  DEALLOCATE PREPARE stmt;
 
select      t1.periode,
            t1.the_value as val1,
            t2.the_value as val2
 
      from  `temp1`  as t1
 
inner join  `temp2`  as t2
        on  t2.periode = t1.periode;
 
explain
select      t1.periode,
            t1.the_value as val1,
            t2.the_value as val2
 
      from  `temp1`  as t1
 
inner join  `temp2`  as t2
        on  t2.periode = t1.periode;
 
  drop temporary table if exists `temp1`;
  drop temporary table if exists `temp2`;
END
--------------
 
--------------
call `trait` (5)
--------------
 
+------------+--------+--------+
| periode    | val1   | val2   |
+------------+--------+--------+
| 1549963220 | 100.44 | 210.44 |
| 1549963225 | 115.44 | 220.44 |
| 1549963230 | 140.44 | 230.44 |
| 1549963235 | 165.44 | 240.44 |
| 1549963240 | 190.44 | 250.44 |
+------------+--------+--------+
+----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | periode       | NULL    | NULL    | NULL            |    5 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | periode       | periode | 9       | base.t1.periode |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
--------------
commit
--------------
 
--------------
call `trait` (15)
--------------
 
+------------+--------+--------+
| periode    | val1   | val2   |
+------------+--------+--------+
| 1549963215 | 100.44 | 200.44 |
| 1549963230 | 115.44 | 220.44 |
| 1549963245 | 190.44 | 250.44 |
+------------+--------+--------+
+----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | periode       | NULL    | NULL    | NULL            |    3 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | periode       | periode | 9       | base.t1.periode |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+---------+---------+-----------------+------+----------+-------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Mais cela nécessite de recalculer la périodicité pour les deux tables.

@+

[ben500fr]

Bonjour,

Un couple par intervalle de 5 secondes, si le couple existe. Il faut aussi envisager des trous.

Non, c'est l'inverse. Si vous avez un couple toutes les cinq secondes, si vous en voulez disons cinq fois plus, l'intervalle sera de 1 seconde.
En passant à 15 secondes vis-à-vis des 5 secondes, vous aurez trois fois moins de couple.

Je comprend votre analyse qui est theoriquement juste, mais il y a un MAIS!

Imaginons que les données arrivent une fois toutes les 5 secondes. Elles peuvent arriver au meme moment (meme packet = meme milliseconde), ou arriver avec 2.5 secondes d’écart. Sauf que dans la vrai vie ce débit peut varier...
Donc ce que va faire l'utilisateur: créer un graphe x/y avec une jointure datetime(3). Il risque d'obtenir 0 couples.
Alors il va tenter le jointure sur le datetime(0): il va obtenir 10.000 couple (a la grosse louche ) vu qu'il ne tombera presque que sur des trous
Alors il va faire une jointure "a 5 secondes près": il obtiendra 1 millions de couples.
Mais si il passe "a 15 secondes près" il risque (c'est même sure) de diviser le nombre de couples par 3.


Potentiellement on veut faire un graphe pour n'importe quel couple de paramètres -> du coup j'aime beaucoup la solution de faire des colonnes the_time_bis en datetime(0), puis une colonne a 5 sec pres. Voir une a 1 minute pres si besoin.
D’où ma question sur la façon de créer la colonne a 5s près.

En tous cas je réalise que faire un tracé avec autre chose que tu temps sur l'axe X n'est pas si évident qu'il n'y parait!

Code :

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

ALTER TABLE `T1` ADD `the_time_5_s` DATETIME generated always AS (FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`the_time`)/(5))*(5)))

Le code ci-dessus me renvoie l'erreur #1901 - function or expression is not allow for column the_time_5_s

Pourtant un

Code :

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

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`the_time`)/(5))*(5)) FROM T1

marche parfaitement.

[Artemus24]

Salut ben500fr.

Je comprend votre analyse qui est theoriquement juste, mais il y a un MAIS!

Je comprends votre analyse car dans une périodicité disons de 1 seconde, vous avez plusieurs milliers de lignes à votre disposition, tandis que je n'extrait qu'une seule ligne.

Elles peuvent arriver au meme moment (meme packet = meme milliseconde), ou arriver avec 2.5 secondes d’écart.

Le flux entrant pose un problème si vos données arrivent par paquet.
C'est-à-dire ayant le même timestamp à la milliseconde. Pourquoi ?
Parce que vous aurez des doublons, et seule la première ligne sera prise, les autres seront rejetées.
De ce fait, vous avez un problème de stockage puisque vous ne récupérez pas tout ce que vos satellite vous transmettent.

Sauf que dans la vrai vie ce débit peut varier...

Ce n'est pas un problème de débit, mais de savoir quoi faire de la totalité du flux entrant, afin d'avoir un graphique conforme à vos attentes.

Donc ce que va faire l'utilisateur: créer un graphe x/y avec une jointure datetime(3). Il risque d'obtenir 0 couples.

Oui, j'avais compris votre problème de mise en relation.

Alors il va tenter le jointure sur le datetime(0): il va obtenir 10.000 couple (a la grosse louche ) vu qu'il ne tombera presque que sur des trous.

Justement, quel est le critère de sélection de la ligne pour une périodicité de 1 seconde ?
En réduisant vous 10.000 couples à un seul, vous avez une perte importante d'informations liées à la quantité.

Alors il va faire une jointure "a 5 secondes près": il obtiendra 1 millions de couples.

C'est encore pire car vous réduisez à 1 seul couple, la quantité de 1 million.

Et je reformule le problème. Comment sélectionnez le couple parmi toutes celles qui sont à votre disposition ?
Le premier arrivé est le premier servie ? Le minimum ? Le maximum ? Autre ?

Mais si il passe "a 15 secondes près" il risque (c'est même sure) de diviser le nombre de couples par 3.

Ok, nous sommes d'accord et dans ce cas que préconisez-vous ?
Je parle de la perte d'informations liées à la réduction de la quantité de lignes que vous avez pour la périodicité de 15 secondes.

Potentiellement on veut faire un graphe pour n'importe quel couple de paramètres

Quelle est la liste des valeurs de vos paramètres (la périodicité) ?
--> 1 seconde
--> 5 secondes
--> 10 secondes.
--> 15 secondes.

La liste risque d'être longue, juste pour satisfaire l'utilisateur dans la finesse de la recherche du graphe parfait.

du coup j'aime beaucoup la solution de faire des colonnes the_time_bis en datetime(0), puis une colonne a 5 sec pres. Voir une a 1 minute pres si besoin.

Je comprends votre satisfaction, sauf à quoi cela sert de stocker autant d'informations si celles-ci seront jamais toutes utilisées ?
La table risque d'augmenter en volumétrie inutilement.
Mais cela ne va pas résoudre votre problème si un utilisateur demande une périodicité que vous n'avez pas prévue dans la liste des paramètres.

D’où ma question sur la façon de créer la colonne a 5s près.

Vous ne pouvez plus travailler sur la date + time + milliseconde, mais sur la date au format unix, c'est-à-dire une quantité numérique.
Après, le stockage peut très bien se faire au format date + time + milliseconde.
Mais le fait de faire une mise en forme de vos données risque d'allonger le temps de stockage de vos données.

En tous cas je réalise que faire un tracé avec autre chose que tu temps sur l'axe X n'est pas si évident qu'il n'y parait!

Ce n'est pas si évident de laisser le choix à l'utilisateur dans la périodicité, sachant que vous ne disposez pas de cette information au préalable.
D'où une augmentation de la volumétrie , ou bien une perte dans la performance, et ce, à cause de la préparation en calcul pour répondre au graphe de l'utilisateur.

Normalement, dans une base de données, on fige les relations que l'on désire faire. D'où la performance dans l'optention du résultat.
Mais si ce que vous demandez n'a pas été prévue dès le départ, vous aurez une perte en performance.

On peut améliorer la performance en sélectionnant un intervalle de temps comprenant le minimum et le maximum.

@+

[ben500fr]

Bonjour,
j'ai pris le temps de réfléchir et de discuter avec mes collègues.

Ok, nous sommes d'accord et dans ce cas que préconisez-vous ?
Je parle de la perte d'informations liées à la réduction de la quantité de lignes que vous avez pour la périodicité de 15 secondes.

Pour afficher un paramètre dans le temps, il faut afficher toutes les valeurs sur une période, sans corruption possible. Ca c'est bon c'est fait.
Pour un graphe avec un paramètre sur l'axe x, on recherche des tendances, on s'autorise des pertes de donnes. L'outil l'affichera de manière très explicite mais j'ai un "go" des équipes.

Quelle est la liste des valeurs de vos paramètres (la périodicité) ?
--> 1 seconde
--> 5 secondes
--> 10 secondes.
--> 15 secondes.

La liste risque d'être longue, juste pour satisfaire l'utilisateur dans la finesse de la recherche du graphe parfait.

La periodicite de reception n'est pas fixe, et peut aller jusqu’à 1 valeur toutes les 10 minutes.
Je vais proposer a l'utilisateur des jointures a la milliseconde, la secondes, 5s, et 15s. On verra si on en veut plus de possibilités. Le service informatique est prêt a me fournir des bases/serveurs et même des arrays de stockage a volonté si on satisfait notre besoin. Mais pour le moment un serveur sous Linux connecte sur un gros SAN (armoire remplie de disques SSD) me suffis.
De toutes façons les colonnes avec les arrondi ne seront créées que sur demande, donc pas sur tous les paramètres.

Je comprends votre satisfaction, sauf à quoi cela sert de stocker autant d'informations si celles-ci seront jamais toutes utilisées ?
La table risque d'augmenter en volumétrie inutilement.
Mais cela ne va pas résoudre votre problème si un utilisateur demande une périodicité que vous n'avez pas prévue dans la liste des paramètres.

On sauvegarde en l’état TOUTES les telemetries reçu. On considère que c'est le bien le plus précieux que nous possédons.
Et pour info il n'est pas rare de faire des analyse sur un paramètre avec un "min/max/moyenne par heure" sur une longue période, et au bout de 5 ans, souhaiter analyser les 5 dernières années a la grosse loupe. On ne sait jamais en avance ce qu'on veut voir. Un exemple typique: le voltage de chaque cellule des batteries.




Je bloque toujours sur ce code qui ne marche pas pour ajouter la colonne a 5s pres:

Code :

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

ALTER TABLE `T1` ADD `the_time_5_s` DATETIME generated always AS (FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`the_time`)/(5))*(5)))

[Artemus24]

Salut ben500fr.

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `t1`
--------------
 
--------------
CREATE TABLE `t1`
( `the_decimal`  decimal(15,3)                                                                       NOT NULL PRIMARY KEY,
  `the_value`    float                                                                               NOT NULL,
  `the_time`     timestamp(3) generated always AS (FROM_UNIXTIME(      the_decimal       ))  virtual NOT NULL,
  `the_time_05`  timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/ 5)* 5))  virtual NOT NULL,
  `the_time_15`  timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/15)*15))  virtual NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t1` (`the_decimal`,`the_value`) values
  (unix_timestamp('2019-02-12 10:20:00.123'),100.44),
  (unix_timestamp('2019-02-12 10:20:01.123'),102.44),
  (unix_timestamp('2019-02-12 10:20:02.123'),104.44),
  (unix_timestamp('2019-02-12 10:20:03.123'),106.44),
  (unix_timestamp('2019-02-12 10:20:04.123'),108.44),
  (unix_timestamp('2019-02-12 10:20:05.123'),110.44),
  (unix_timestamp('2019-02-12 10:20:06.123'),112.44),
  (unix_timestamp('2019-02-12 10:20:07.123'),114.44),
  (unix_timestamp('2019-02-12 10:20:08.123'),116.44),
  (unix_timestamp('2019-02-12 10:20:09.123'),118.44),
  (unix_timestamp('2019-02-12 10:20:10.123'),120.44),
  (unix_timestamp('2019-02-12 10:20:11.123'),122.44),
  (unix_timestamp('2019-02-12 10:20:12.123'),124.44),
  (unix_timestamp('2019-02-12 10:20:13.123'),126.44),
  (unix_timestamp('2019-02-12 10:20:14.123'),128.44),
  (unix_timestamp('2019-02-12 10:20:15.123'),130.44),
  (unix_timestamp('2019-02-12 10:20:16.123'),132.44),
  (unix_timestamp('2019-02-12 10:20:17.123'),134.44),
  (unix_timestamp('2019-02-12 10:20:18.123'),136.44),
  (unix_timestamp('2019-02-12 10:20:19.123'),138.44),
  (unix_timestamp('2019-02-12 10:20:20.123'),140.44),
  (unix_timestamp('2019-02-12 10:20:21.123'),142.44),
  (unix_timestamp('2019-02-12 10:20:22.123'),144.44),
  (unix_timestamp('2019-02-12 10:20:23.123'),146.44),
  (unix_timestamp('2019-02-12 10:20:24.123'),148.44),
  (unix_timestamp('2019-02-12 10:20:25.123'),150.44),
  (unix_timestamp('2019-02-12 10:20:26.123'),152.44),
  (unix_timestamp('2019-02-12 10:20:27.123'),154.44),
  (unix_timestamp('2019-02-12 10:20:28.123'),156.44),
  (unix_timestamp('2019-02-12 10:20:29.123'),158.44),
  (unix_timestamp('2019-02-12 10:20:30.123'),160.44),
  (unix_timestamp('2019-02-12 10:20:31.123'),162.44),
  (unix_timestamp('2019-02-12 10:20:32.123'),164.44),
  (unix_timestamp('2019-02-12 10:20:33.123'),166.44),
  (unix_timestamp('2019-02-12 10:20:34.123'),168.44),
  (unix_timestamp('2019-02-12 10:20:35.123'),170.44),
  (unix_timestamp('2019-02-12 10:20:36.123'),172.44),
  (unix_timestamp('2019-02-12 10:20:37.123'),174.44),
  (unix_timestamp('2019-02-12 10:20:38.123'),176.44),
  (unix_timestamp('2019-02-12 10:20:39.123'),178.44)
--------------
 
--------------
select * from `t1`
--------------
 
+----------------+-----------+-------------------------+---------------------+---------------------+
| the_decimal    | the_value | the_time                | the_time_05         | the_time_15         |
+----------------+-----------+-------------------------+---------------------+---------------------+
| 1549963200.123 |    100.44 | 2019-02-12 10:20:00.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963201.123 |    102.44 | 2019-02-12 10:20:01.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963202.123 |    104.44 | 2019-02-12 10:20:02.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963203.123 |    106.44 | 2019-02-12 10:20:03.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963204.123 |    108.44 | 2019-02-12 10:20:04.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963205.123 |    110.44 | 2019-02-12 10:20:05.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963206.123 |    112.44 | 2019-02-12 10:20:06.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963207.123 |    114.44 | 2019-02-12 10:20:07.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963208.123 |    116.44 | 2019-02-12 10:20:08.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963209.123 |    118.44 | 2019-02-12 10:20:09.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963210.123 |    120.44 | 2019-02-12 10:20:10.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963211.123 |    122.44 | 2019-02-12 10:20:11.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963212.123 |    124.44 | 2019-02-12 10:20:12.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963213.123 |    126.44 | 2019-02-12 10:20:13.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963214.123 |    128.44 | 2019-02-12 10:20:14.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963215.123 |    130.44 | 2019-02-12 10:20:15.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963216.123 |    132.44 | 2019-02-12 10:20:16.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963217.123 |    134.44 | 2019-02-12 10:20:17.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963218.123 |    136.44 | 2019-02-12 10:20:18.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963219.123 |    138.44 | 2019-02-12 10:20:19.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963220.123 |    140.44 | 2019-02-12 10:20:20.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963221.123 |    142.44 | 2019-02-12 10:20:21.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963222.123 |    144.44 | 2019-02-12 10:20:22.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963223.123 |    146.44 | 2019-02-12 10:20:23.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963224.123 |    148.44 | 2019-02-12 10:20:24.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963225.123 |    150.44 | 2019-02-12 10:20:25.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963226.123 |    152.44 | 2019-02-12 10:20:26.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963227.123 |    154.44 | 2019-02-12 10:20:27.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963228.123 |    156.44 | 2019-02-12 10:20:28.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963229.123 |    158.44 | 2019-02-12 10:20:29.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963230.123 |    160.44 | 2019-02-12 10:20:30.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963231.123 |    162.44 | 2019-02-12 10:20:31.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963232.123 |    164.44 | 2019-02-12 10:20:32.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963233.123 |    166.44 | 2019-02-12 10:20:33.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963234.123 |    168.44 | 2019-02-12 10:20:34.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963235.123 |    170.44 | 2019-02-12 10:20:35.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963236.123 |    172.44 | 2019-02-12 10:20:36.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963237.123 |    174.44 | 2019-02-12 10:20:37.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963238.123 |    176.44 | 2019-02-12 10:20:38.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963239.123 |    178.44 | 2019-02-12 10:20:39.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
+----------------+-----------+-------------------------+---------------------+---------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

[ben500fr]

Salut ben500fr.

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `t1`
--------------
 
--------------
CREATE TABLE `t1`
( `the_decimal`  decimal(15,3)                                                                       NOT NULL PRIMARY KEY,
  `the_value`    float                                                                               NOT NULL,
  `the_time`     timestamp(3) generated always AS (FROM_UNIXTIME(      the_decimal       ))  virtual NOT NULL,
  `the_time_05`  timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/ 5)* 5))  virtual NOT NULL,
  `the_time_15`  timestamp(0) generated always AS (FROM_UNIXTIME(floor(the_decimal/15)*15))  virtual NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `t1` (`the_decimal`,`the_value`) values
  (unix_timestamp('2019-02-12 10:20:00.123'),100.44),
  (unix_timestamp('2019-02-12 10:20:01.123'),102.44),
  (unix_timestamp('2019-02-12 10:20:02.123'),104.44),
  (unix_timestamp('2019-02-12 10:20:03.123'),106.44),
  (unix_timestamp('2019-02-12 10:20:04.123'),108.44),
  (unix_timestamp('2019-02-12 10:20:05.123'),110.44),
  (unix_timestamp('2019-02-12 10:20:06.123'),112.44),
  (unix_timestamp('2019-02-12 10:20:07.123'),114.44),
  (unix_timestamp('2019-02-12 10:20:08.123'),116.44),
  (unix_timestamp('2019-02-12 10:20:09.123'),118.44),
  (unix_timestamp('2019-02-12 10:20:10.123'),120.44),
  (unix_timestamp('2019-02-12 10:20:11.123'),122.44),
  (unix_timestamp('2019-02-12 10:20:12.123'),124.44),
  (unix_timestamp('2019-02-12 10:20:13.123'),126.44),
  (unix_timestamp('2019-02-12 10:20:14.123'),128.44),
  (unix_timestamp('2019-02-12 10:20:15.123'),130.44),
  (unix_timestamp('2019-02-12 10:20:16.123'),132.44),
  (unix_timestamp('2019-02-12 10:20:17.123'),134.44),
  (unix_timestamp('2019-02-12 10:20:18.123'),136.44),
  (unix_timestamp('2019-02-12 10:20:19.123'),138.44),
  (unix_timestamp('2019-02-12 10:20:20.123'),140.44),
  (unix_timestamp('2019-02-12 10:20:21.123'),142.44),
  (unix_timestamp('2019-02-12 10:20:22.123'),144.44),
  (unix_timestamp('2019-02-12 10:20:23.123'),146.44),
  (unix_timestamp('2019-02-12 10:20:24.123'),148.44),
  (unix_timestamp('2019-02-12 10:20:25.123'),150.44),
  (unix_timestamp('2019-02-12 10:20:26.123'),152.44),
  (unix_timestamp('2019-02-12 10:20:27.123'),154.44),
  (unix_timestamp('2019-02-12 10:20:28.123'),156.44),
  (unix_timestamp('2019-02-12 10:20:29.123'),158.44),
  (unix_timestamp('2019-02-12 10:20:30.123'),160.44),
  (unix_timestamp('2019-02-12 10:20:31.123'),162.44),
  (unix_timestamp('2019-02-12 10:20:32.123'),164.44),
  (unix_timestamp('2019-02-12 10:20:33.123'),166.44),
  (unix_timestamp('2019-02-12 10:20:34.123'),168.44),
  (unix_timestamp('2019-02-12 10:20:35.123'),170.44),
  (unix_timestamp('2019-02-12 10:20:36.123'),172.44),
  (unix_timestamp('2019-02-12 10:20:37.123'),174.44),
  (unix_timestamp('2019-02-12 10:20:38.123'),176.44),
  (unix_timestamp('2019-02-12 10:20:39.123'),178.44)
--------------
 
--------------
select * from `t1`
--------------
 
+----------------+-----------+-------------------------+---------------------+---------------------+
| the_decimal    | the_value | the_time                | the_time_05         | the_time_15         |
+----------------+-----------+-------------------------+---------------------+---------------------+
| 1549963200.123 |    100.44 | 2019-02-12 10:20:00.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963201.123 |    102.44 | 2019-02-12 10:20:01.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963202.123 |    104.44 | 2019-02-12 10:20:02.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963203.123 |    106.44 | 2019-02-12 10:20:03.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963204.123 |    108.44 | 2019-02-12 10:20:04.123 | 2019-02-12 10:20:00 | 2019-02-12 10:20:00 |
| 1549963205.123 |    110.44 | 2019-02-12 10:20:05.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963206.123 |    112.44 | 2019-02-12 10:20:06.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963207.123 |    114.44 | 2019-02-12 10:20:07.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963208.123 |    116.44 | 2019-02-12 10:20:08.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963209.123 |    118.44 | 2019-02-12 10:20:09.123 | 2019-02-12 10:20:05 | 2019-02-12 10:20:00 |
| 1549963210.123 |    120.44 | 2019-02-12 10:20:10.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963211.123 |    122.44 | 2019-02-12 10:20:11.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963212.123 |    124.44 | 2019-02-12 10:20:12.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963213.123 |    126.44 | 2019-02-12 10:20:13.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963214.123 |    128.44 | 2019-02-12 10:20:14.123 | 2019-02-12 10:20:10 | 2019-02-12 10:20:00 |
| 1549963215.123 |    130.44 | 2019-02-12 10:20:15.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963216.123 |    132.44 | 2019-02-12 10:20:16.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963217.123 |    134.44 | 2019-02-12 10:20:17.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963218.123 |    136.44 | 2019-02-12 10:20:18.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963219.123 |    138.44 | 2019-02-12 10:20:19.123 | 2019-02-12 10:20:15 | 2019-02-12 10:20:15 |
| 1549963220.123 |    140.44 | 2019-02-12 10:20:20.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963221.123 |    142.44 | 2019-02-12 10:20:21.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963222.123 |    144.44 | 2019-02-12 10:20:22.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963223.123 |    146.44 | 2019-02-12 10:20:23.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963224.123 |    148.44 | 2019-02-12 10:20:24.123 | 2019-02-12 10:20:20 | 2019-02-12 10:20:15 |
| 1549963225.123 |    150.44 | 2019-02-12 10:20:25.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963226.123 |    152.44 | 2019-02-12 10:20:26.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963227.123 |    154.44 | 2019-02-12 10:20:27.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963228.123 |    156.44 | 2019-02-12 10:20:28.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963229.123 |    158.44 | 2019-02-12 10:20:29.123 | 2019-02-12 10:20:25 | 2019-02-12 10:20:15 |
| 1549963230.123 |    160.44 | 2019-02-12 10:20:30.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963231.123 |    162.44 | 2019-02-12 10:20:31.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963232.123 |    164.44 | 2019-02-12 10:20:32.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963233.123 |    166.44 | 2019-02-12 10:20:33.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963234.123 |    168.44 | 2019-02-12 10:20:34.123 | 2019-02-12 10:20:30 | 2019-02-12 10:20:30 |
| 1549963235.123 |    170.44 | 2019-02-12 10:20:35.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963236.123 |    172.44 | 2019-02-12 10:20:36.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963237.123 |    174.44 | 2019-02-12 10:20:37.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963238.123 |    176.44 | 2019-02-12 10:20:38.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
| 1549963239.123 |    178.44 | 2019-02-12 10:20:39.123 | 2019-02-12 10:20:35 | 2019-02-12 10:20:30 |
+----------------+-----------+-------------------------+---------------------+---------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

Salut,

Ma version mySQL refuse le "NOT NULL" apres "virtual" mais sinon ton code marche

Deux choses qui me manquent:

• je souhaite ajouter les colonnes avec les dates "arrondis" après la création de la table.
• ma clé primaire est de type datetime(3), et non decimal(15,3).

Du coup voici ce que j'essaie de faire:

Code :

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

ALTER TABLE DB1.`T1` ADD `the_time_05` timestamp(0) generated always AS (FROM_UNIXTIME(floor(UNIX_TIMESTAMP(`the_time`)/ 5)* 5)) virtual

mais ca ne marche pas a cause du "UNIX_TIMESTAMP" qui ne veut pas etre pris ici...
Du coup j'ai un brin modifie (merci Google...) et avec ceci ça marche très bien:

Code :

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

ALTER TABLE DB1.`T1` ADD `the_time_05` timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', `the_time`)/ 5)* 5)) persistent

C'est assez moche je trouve de faire la soustraction manuellement mais bon... A partir de maintenant si je ne touche plus a rien j'obtiens les résultats souhaites avec des performances acceptable.

Vaut il mieux mettre un virtual ou un persistent a la fin?


Le probleme actuel: lors de la jointure, avec un "LIMIT 500" a la fin de la requete:

• si je fais la jointure sue le the_time => query took 0.3s
• si je fais la jointure sue le the_time_05 => query took 6s

Les performances sont catastrophiques! De ce que je comprends les valeurs "the_time_05" arrondi a 5s près ne sont pas sauvegardées dans la base, mais sont recalculées a chaque fois que necessaire...
Comment forcer la base a garder ces valeurs de manière "statique"?

Merci encore pour vos réponses, vous me sortez vraiment la tete des ronces (pour ne pas dire autre chose)!

[Artemus24]

Salut ben500fr.

Ma version mySQL refuse le "NOT NULL" apres "virtual" mais sinon ton code marche

Quelle est la version MySql que vous utilisez ? J'utilise la version 8.0.15.

je souhaite ajouter les colonnes avec les dates "arrondis" après la création de la table.

N'est-ce pas ce que je fais dans mon exemple ?

ma clé primaire est de type datetime(3), et non decimal(15,3).

J'ai cherché à comprendre pourquoi votre exemple, ne fonctionnait pas :

Code :

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

ALTER TABLE `T1` ADD `the_time_5_s` DATETIME generated always AS (FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`the_time`)/(5))*(5)))

J'ai fait des recherches et MySql n'accepte pas la fonction "UNIX_TIMESTAMP" dans une colonne calculée (GENERATED ALWAYS).
De ce fait, la seule astuce que j'ai trouvé est de recevoir la date sous le format unix, à savoir, un nombre décimal.
A partir de là, la structure de la table change.

mais ca ne marche pas a cause du "UNIX_TIMESTAMP" qui ne veut pas etre pris ici...

Justement, je ne comprends pas pourquoi MySql interdit l'utilisation de cette fonction dans les colonnes calculées.

Du coup j'ai un brin modifie (merci Google...) et avec ceci ça marche très bien:

J'avais trouvé sur le net cette astuce, consistant à passer par la fonction "TIMESTAMPDIFF()", qui est la solution de remplacement de la fonction "unix_timestamp()".
Mais quand j'ai testé, cela n'a pas fonctionné.

Vaut il mieux mettre un virtual ou un persistent à la fin?

Le "virtual" permet de ne pas stocker le calcul dans la ligne de la table.
Le "stored", c'est l'inverse, vous stockez le calcul dans la ligne.

Par contre, je ne vois pas où vous avez trouvé le "persistent" ???
--> https://dev.mysql.com/doc/refman/8.0...d-columns.html

J'ai compris, vous êtes dans Mariadb !
--> https://mariadb.com/kb/en/library/generated-columns/
On lit :

Virtual columns could only use deterministic functions. Since MariaDB 10.2.1 this limitation applies only to PERSISTENT columns.
Only PERSISTENT columns can be indexed.

Je préfère le "virtual" car la calcul ne sera pas stockée dans la table.
Sauf que pour des raisons de performances, vous devez les indexer !
Donc, vous n'avez pas le choix, c'est obligatoirement "persistent".

Les performances sont catastrophiques !

Vous devez indexer votre colonne "the_time_05". D'où l'utilité de mettre "persistent".

Comment forcer la base a garder ces valeurs de manière "statique"?

Si vous les stocker vous aurez un problème de volumétrie. Sinon, en mettant "stored" à la place de "virtual".

@+

[ben500fr]

Salut ben500fr.
Quelle est la version MySql que vous utilisez ? J'utilise la version 8.0.15.

MariaDB 5.5.60

J'ai fait des recherches et MySql n'accepte pas la fonction "UNIX_TIMESTAMP" dans une colonne calculée (GENERATED ALWAYS).
De ce fait, la seule astuce que j'ai trouvé est de recevoir la date sous le format unix, à savoir, un nombre décimal.
A partir de là, la structure de la table change.

J'avais bien intuité que MariaDB n'accepte pas la fonction "UNIX_TIMESTAMP" dans certains cas, mais je ne l'avais pas trouve dans la doc... Merci de cette confirmation!

Vous devez indexer votre colonne "the_time_05". D'où l'utilité de mettre "persistent".

Je venais justement éditer mon message pour dire que je suis un boulet: je n'ai pas indexé la colonne!
Comme vous pouvez l'imaginer mes bases sont un poil plus complexe que ce que je vous montre ici, j'ai des scripts bash pour créer/éditer/réinitialiser mes bases/tables/vues a la volée. Et la ligne qui ajoute l'index été commentée!
Bref une vue qui prenais 20 minutes prend maintenant 0.0007 secondes a être générée avec 330.700 lignes a la sortie. C'est fou! (et ce n'est que le début)

Je vais déclarer le sujet résolu maintenant. Et vous dire un grand MERCI!
En plus de quasiment faire le travail a ma place, vous m'avez amené a me poser les bonnes questions et a mieux réfléchir ma façon appréhender mes structures de tables.

[Artemus24]

Salut ben500fr.

En plus de quasiment faire le travail à ma place, vous m'avez amené à me poser les bonnes questions et à mieux réfléchir ma façon appréhender mes structures de tables.

Le but de nos échanges est justement de réfléchir à une meilleur solution.

La solution basée sur les colonnes calculées (generated always) à le mérite de faire la calcul virtuellement et de pouvoir l'utiliser par la suite.
Elle n'alourdie pas la volumétrie de la table, et prépare un tant soit peu le travail à faire.
L'inconvénient est le nombre de colonnes que l'on doit créer pour chaque test à faire.
Comme le temps d'exécution est une contrainte importante, je ne vois pas d'autre solution que celle que je vous ai proposé.

Il existe une autre solution qui consiste à dupliquer les lignes dans votre table en spéciant le type de précisions appliquées sur la colonne "the_time".
Par exemple :
--> type = 1, "the_time" est non modifié
--> type = 2, "the_time" est à 1 seconde près.
--> type = 3, "the_time" est à 5 secondes près.
--> type = 4, "the_time" est à 15 secondes près.
Et ainsi de suite. Elle a surtout le mérite d'être très volumineuse.

Si vous êtes près à perdre du temps, le mieux aurait été de créer une table temporaire pour la mise en forme de votre précision.

Après, je ne vois rien d'autre.

@+