Discussion :

[pierrot10]

Bonjour,

J'ai une question bête, mais vu que ma base de donnée à plus d'une million de mesures (enfin pas pour toutes les stations) je me demande si ce point est relevant.

J'ai une API qui va extraire les données de ma base de donnée en fonction d'une station. Si la date est inconnue, l'API doit retourner la date du dernier enregistrement

Code PHP :

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
 
if($to == "0000-00-00 00:00:00"){
 
			$sql_select = 'SELECT c.ttn_m_time FROM stations AS st
				INNER JOIN sensors AS s ON st.id_station = s.stations_id_station
				INNER JOIN measures AS m ON s.id_sensor = m.sensors_id_sensor
				INNER JOIN collections AS c ON m.collections_id_collection = c.id_collection
				WHERE st.id_station = '.$station.'
				ORDER BY c.ttn_m_time DESC LIMIT 1';
 
			$sql_result = $connect->query($sql_select);
 
			while($row = $sql_result->fetch_assoc()){
				foreach($row as $r => $rr){
					$to = $rr;					}
			}
		}

Mon problème est que si je sélectionne des dates (from/to), la date de la dernière mesure prend la valeur à la la date TO, alors que j'aimerais qu'elle garde la valeur de la date dernière mesure enregistrée. J'ai besoin de cette date pour sélectionner les dates sélectionnables demanière à ce qu'elles ne dépassent pas la date de la dernière mesure enregistrée.

Je pourrais donc supprimer la condition

Code PHP :

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

if($to == "0000-00-00 00:00:00")

pour que ce code soit toujours exécuté même si plus bas, je le réexécute mais sans le LIMIT 1 et avec d'autres critères pour élargir les données que je dois recevoir.

On est d'accord que le script ci-dessus, prend très peu de ressource? C'est à dire, il met un tout petit temps à retourner la date?
On est d'accord que vu que j'ai ceci

Code PHP :

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

ORDER BY c.ttn_m_time DESC LIMIT 1';

, il va "s'arrêter" dès qu'il li la première ligne?

[escartefigue]

Bonjour,

Extraire une seule ou quelques lignes d'une table contenant des centaines de millions ou des milliards de lignes peut être très performant si une restriction rend un index éligible pour filtrer la sélection.
À l'inverse, faire comme dans votre requête un tri de toutes les lignes pour ne conserver que le "top 1" est contre performant et peut durer des plombes

A priori, une station possède 1 à plusieurs capteurs et un capteur effectue zéro à plusieurs mesures.
Mais à quoi sert la table "collection" ?

Du coup, il faut probablement (sous réserve des explications relatives à la table "collection"), créer un index sur la table des mesures ayant pour colonnes, dans cet ordre, l'identifiant du capteur et l'horodatage de la mesure et utiliser une restriction qui porte uniquement sur la table des mesures en sélectionnant l'horodatage max de la mesure. La jointure entre capteur et station permettra de vérifier qu'on est sur la bonne station.

Communiquez le script DDL de création des tables et index pour qu'on puisse vous communiquer un exemple.

Notes :

• parler d'"enregistrement" est inadéquat, dans une base de données relationnelle, on parle de "lignes" ;
• l'usage est de nommer les tables au singulier. Par exemple "collection" plutôt que "collections". Il est évident qu'une table peut contenir plusieurs occurrences

[pierrot10]

Hello
Merci pour ta réponse.

Oui c'est juste, une station a des capteurs et chaque capteur prend des mesures. Mais les capteurs ne prennent pas les mesures en même temps, mais un après l'autre. Ceci dit, il faut quelques secondes (2 à 10 secondes en fonction du nombre de capteur) pour qu'une station.
J'ai une table qui liste mes stations
Une table qui liste tous les capteurs disponibles. Ils sont liés à une station. Les sations n'ont pas tous les mêmes capteurs.
Et les mesures sont enregistrés dans la tables 'measures'.

Une station prend des mesures toutes x minutes, ce que j'appelle une collection, soit une collection de mesures, et la date (ttn_m_time) que je recherche est dans la table 'collections' de manière à avoir une date/heure pour la prise de mesure.

La table 'collections' est liée à la table 'measures'
la table 'measures' est liée à la table 'sensors' et cette dernière est liée à la table 'stations'

À l'inverse, faire comme dans votre requête un tri de toutes les lignes pour ne conserver que le "top 1" est contre performant et peut durer des plombes

Je comprends, mais honnetement je ne peux pas repenser (améliorer) à ca maintenant. Ca me semble monstrueux de changer toute la structure et les scripts qui sont liés à ceci, non?

Dans ma requête

Code PHP :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
$sql_select = 'SELECT c.ttn_m_time FROM stations AS st
	INNER JOIN sensors AS s ON st.id_station = s.stations_id_station
	INNER JOIN measures AS m ON s.id_sensor = m.sensors_id_sensor
	INNER JOIN collections AS c ON m.collections_id_collection = c.id_collection
	WHERE st.id_station = '.$station.'
	ORDER BY c.ttn_m_time DESC LIMIT 1';
 
$sql_result = $connect->query($sql_select);
 
while($row = $sql_result->fetch_assoc()){
	foreach($row as $r => $rr){
		$to = $rr;
        }
}

Il n'y a quei les champs

st.id_station, s.stations_id_station, s.id_sensor, m.sensors_id_sensor, m.collections_id_collection, c.id_collection et c.ttn_m_time

Ormis la table 'collections' les autres tables ne sont pas parcourues, non?

Ne va-t-il pas parcours la tables 'collections' de la date la plus récentes à la plus ancienne, jusqu'à ce qu'il trouve la date la plus récente de la "collection" correspondant à la station sélectionnée? Mais est-ce que ceci peut prendre plus de 0.5 seconde, même si table 'collections' est conséquence, mais pas autant que la table 'measures'?

Merciiii

[escartefigue]

Donc on a le modèle de données suivant :





Qui donne le DDL suivant, si on sélectionne MySQL dans Looping :

Code SQL :

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
CREATE TABLE STA_station(
   STA_ident INT AUTO_INCREMENT,
   STA_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(STA_ident)
);
 
CREATE TABLE CAP_capteur(
   CAP_ident INT AUTO_INCREMENT,
   CAP_reference CHAR(8) NOT NULL,
   STA_ident INT NOT NULL,
   PRIMARY KEY(CAP_ident),
   FOREIGN KEY(STA_ident) REFERENCES STA_station(STA_ident)
);
 
CREATE TABLE MES_mesure(
   CAP_ident INT,
   MES_seqn SMALLINT,
   MES_valeur DECIMAL(9,3) NOT NULL,
   MES_timest DATETIME NOT NULL,
   PRIMARY KEY(CAP_ident, MES_seqn),
   FOREIGN KEY(CAP_ident) REFERENCES CAP_capteur(CAP_ident)
);

Je crée un petit jeu d'essai comme suit :

Code SQL :

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
insert into STA_station (STA_nom)
values ('ST1'), ('ST2'), ('ST3')
;
insert into CAP_capteur (CAP_reference, STA_ident)
values ('CAP00001', 1), ('CAP00002', 1), ('CAP00003', 1), 
       ('CAP00004', 2), ('CAP00005', 3), ('CAP00006', 3)
;
insert into MES_mesure (CAP_ident, MES_seqn, MES_valeur, MES_timest)
values (1, 01, 100.00, '2025-05-14 06:00:11')
     , (1, 02, 080.00, '2025-05-14 06:14:25')
     , (1, 03, 103.00, '2025-05-14 06:21:34')  
     , (1, 04, 066.00, '2025-05-14 06:55:02')  
     , (1, 05, 104.00, '2025-05-14 07:02:58')  
     , (1, 06, 092.00, '2025-05-14 07:18:00')  
     , (1, 10, 103.00, '2025-05-15 06:02:20')
     , (1, 11, 082.00, '2025-05-15 06:16:48')
     , (1, 12, 106.00, '2025-05-15 06:21:00')  
     , (1, 13, 100.00, '2025-05-15 06:50:59')  
     , (1, 14, 098.00, '2025-05-15 07:03:06')  
     , (1, 15, 095.00, '2025-05-15 07:18:02')  
     , (2, 01, 100.00, '2025-05-14 06:00:02')
     , (2, 02, 006.00, '2025-05-14 06:15:03')  
     , (2, 03, 006.00, '2025-05-14 06:31:40')  
     , (2, 04, 005.00, '2025-05-14 06:42:23')    
     , (2, 10, 007.00, '2025-05-15 06:18:40')    
     , (2, 11, 005.00, '2025-05-15 06:32:11')    
     , (2, 12, 005.00, '2025-05-15 06:45:40')      
     , (2, 13, 008.00, '2025-05-15 07:00:01')        
     , (2, 14, 006.00, '2025-05-15 07:16:13')          
;

Et voici enfin une requête qui permet de récupérer les mesures cumulées par tranche horaire de la dernière date de mesure de chaque capteur :

Code SQL :

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
select STA.STA_nom           as "Station"
     , CAP.CAP_reference     as "Capteur"
     , max(date(MES_timest)) as "Date"
     , extract(hour from MES_timest) as "Tranche h"   
     , sum(MES.MES_valeur)   as "Total"
from MES_mesure as MES
inner join CAP_capteur as CAP
   on CAP.CAP_ident = MES.CAP_ident
inner join STA_station as STA
   on STA.STA_ident = CAP.STA_ident
where date(MES_timest) =
    (select max(date(MES_timest))
     from MES_mesure subq
     where subq.CAP_ident = MES.CAP_ident
    )
group by STA.STA_nom
       , CAP.CAP_reference
       , extract(hour from MES_timest) 
;

Résultat :




Notes :

• l'intérêt de la table "collection" m'échappe, s'il s'agit simplement d'assembler des périodes de mesure d'un capteur (par exemple toutes les mesures de la même tranche horaire), elle n'est pas utile, et c'est même un risque d'erreurs ;
• dans une table d'une base relationnelle, il n'y a pas de "champs", mais des colonnes

[pierrot10]

Hello
Merci beaucoup, mais t'en fait trop .
Je ne peux pas refaire ma structure maintenant même si je pourrais me repencher dessus.
Ma table 'collections' contient aussi des informations sur la tramnsition des données (LoRaWAN) et je collecte des données sur les passerelles



Même si ca vaudrait la peine de me pencher la dessus, je dois absolument finir mon application IOS/Android. Je pourrais voir ca sous le theme de l'optinisation des requêtes

[escartefigue]

L'idée n'est pas de refaire le modèle de données, mais de montrer comment s'y prendre avec un modèle de données très proche, il y a plus qu'à s'inspirer de la requête que j'ai fournie en adaptant les noms

[pierrot10]

Cher escartefigue

Je te remercie milles fois pour ton aide.
Ca te vas, si je reviens la dessus plus tard?

Pour le moment ca fonctionne bien et je dois vraiment me consacrer à l'application IOS/Android avec comme c'est pour le moment. Mais je suis 100% d'avis que je pourrais améliorer ça en fonction de tes suggestions déjà présentées. J'en tiens compte et je me pencherai la dessus et je n'hésiterai pas à donner une suite à ca.

Je laisse le sujet overt

[escartefigue]

Pas de soucis