Bonjour

j'ai une page sur mon site qui regroupe des informations sur les données météo du jour en cours, mois en cours et années en cours, pour alimenter ce tableau j'ai cette requête pour la température maxi et l'heure du maxi. 

SELECT a.DateTime, r.Tempmax FROM relevemeteo a
INNER JOIN (
   SELECT DATE(a2.DateTime) AS dayOfMax,
      MAX(a2.Tempmax) AS Tempmax
   FROM relevemeteo a2 WHERE DATE(DateTime) = CURDATE()
   GROUP BY DATE(a2.DateTime) ASC
) r
   ON DATE(a.DateTime) = r.dayOfMax
   AND a.Tempmax = r.Tempmax LIMIT 1

j'ai en tout 18 requêtes sur ce modèle pour alimenter ce  tableau et le temps de chargement est environ de 60 secondes, autant dire trop long......

voici un aperçu de ma table : 

Ma question : y a t'il moyen de faire plus simple que les 18 requêtes (jours en cours/mois en cours/année en cours) mais en gardant toute ces informations sur la page et en réduisant considérablement le temps de chargement ?

Dominique

Bonjour,

Un enregistrement toutes les 5 minutes ... depuis 2009 ... cela fait un peu plus de 600 000 lignes c'est ça ?

Y-a-t-il des index sur cette table ? Sur la colonne DateTime notamment ?

Pourquoi 18 requêtes ?

Pourquoi LIMIT 1 ?

As-tu possibilité de modifier la table, notamment éclater la colonne DateTime en deux (date et heure séparées) ?

Benzouye a écrit:

Bonjour,

Un enregistrement toutes les 5 minutes ... depuis 2009 ... cela fait un peu plus de 600 000 lignes c'est ça ?

oui, cela fait beaucoup de lignes et de données...

Y-a-t-il des index sur cette table ? Sur la colonne DateTime notamment ?

la....je n'ai pas construit la table mais il me semble qu'il y a un index dont je ne sais pas a quoi il sert

Pourquoi 18 requêtes ?

une pour chaque information donnée dans le tableau, je ne vois pas trop comment les regrouper et pas sûr même que ce soit plus rapide 

Pourquoi LIMIT 1 ?

car j'obtient plusieurs réponses pour chaque requête et la 1er me suffit

As-tu possibilité de modifier la table, notamment éclater la colonne DateTime en deux (date et heure séparées) ?

En gros tu veux optimiser une requête sans avoir aucun des leviers pour le faire ... ça va être compliqué ...

Tu peux commencer par réduire la taille de la table en déplaçant les données des années précédentes dans une autre table pour ne garder que les données nécessaires (12 mois). Cela va alléger la requête pour l'année en cours.

Après, il me semble que tu peux simplifier la requête présentée ainsi :

SELECT MAX(Tempmax) AS Tempmax
FROM relevemeteo
WHERE DATE(DateTime) = CURDATE()

Cette requête te retourne la température maximale pour la date du jour, ce que semble te retourner la requête présentée au début ...

En déclinant en 3 versions (jour, mois, année) en calculant tout ce dont tu as besoin, cela pourrait seulement faire 3 requêtes :

-- Jour en cours
SELECT
    MAX(Tempmax) AS Tempmax,
    MIN(Tempmax) AS Tempmin,
    AVG(Humidity) AS Humidmoy,
    etc.
FROM relevemeteo
WHERE DATE(DateTime) = CURDATE();

-- Mois en cours
SELECT
    MAX(Tempmax) AS Tempmax,
    MIN(Tempmax) AS Tempmin,
    AVG(Humidity) AS Humidmoy,
    etc.
FROM relevemeteo
WHERE
    MONTH(DateTime) = MONTH(CURDATE())
    AND YEAR(DateTime) = YEAR(CURDATE());

-- Année en cours
SELECT
    MAX(Tempmax) AS Tempmax,
    MIN(Tempmax) AS Tempmin,
    AVG(Humidity) AS Humidmoy,
    etc.
FROM relevemeteo
WHERE YEAR(DateTime) = YEAR(CURDATE());

effectivement c'est plus rapide mais il me manque par exemple la date et l'heure du maxi pour la température.

j'ai cette requête pour les moyenne dont je ne recupère pas la date ,

SELECT AVG(Temp) AS Temp FROM relevemeteo WHERE DATE(DateTime) = CURDATE()

une question peut être stupide...., si j'obtiens une valeur exp : "temp max du jour", y a t'il un moyen de récupérer toutes les valeurs qui se trouve sur la ligne de cette valeurs "temp maxi du jour" ?

-- une table de test
CREATE TABLE zog( dt DATETIME NOT NULL, x FLOAT NOT NULL )
;

-- la table seq contient les entiers de 1 à 1048576
-- on insère une mesure toutes les minutes...
INSERT INTO zog (dt,x) SELECT DATE_ADD( '2000-01-01', INTERVAL 60*id SECOND ), rand() FROM seq;

-- ajoutons un index sur la date
ALTER TABLE zog ADD KEY(dt);

-- voyons voir... OMG c'est ultra lent
SELECT count(*), avg(x) FROM zog WHERE DATE(dt) = '2000-01-22';
+----------+--------------------+
| count(*) | avg(x)             |
+----------+--------------------+
|     1440 | 0.4904482185109777 |
+----------+--------------------+
1 row in set (0.64 sec)

EXPLAIN SELECT count(*) FROM zog WHERE DATE(dt) = '2000-01-22';
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | zog   | index | NULL          | dt   | 8       | NULL | 1049064 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+

L'index n'est pas utilisé 

-- activation d'une neurone :
SELECT count(*), avg(x) FROM zog WHERE dt >= '2000-01-22' AND dt < '2000-01-23';
+----------+--------------------+
| count(*) | avg(x)             |
+----------+--------------------+
|     1440 | 0.4904482185109777 |
+----------+--------------------+
1 row in set (0.00 sec)

-- on vérifie que l'index est bien utilisé
mysql> EXPLAIN SELECT count(*), avg(x) FROM zog WHERE dt >= '2000-01-22' AND dt < '2000-01-23';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | zog   | range | dt            | dt   | 8       | NULL | 1438 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

Bref, 

- on remplace la condition non indexable DATE(dt)=constante par une contition indexable (un intervalle sur la colonne datetime: genre dt>="un jour" AND dt<"le jour suivant")

- on a un index sur la date

- on a une accélération de 6300 environ (0.63s -> 100µs)

Tada!

Merci casque noir

ça parait tellement simple pour toi mais si compliqué pour moi que je ne sais pas me servir de ce que tu a écrit..

merci tout de même !

Pour reformuler ce qu'à bien démontré Lord Casque Noir :

Je te laisse regarder ce qu'est un index.

Si tu ajoutes un index sur la colonne DateTime (si il n'existe pas déjà) alors tu pourras accélérer le temps de traitement de la requête. Toutefois, l'index n'étant pas utilisé si une fonction SQL est utilisée sur la colonne en question (ici la fonction DATE), il faut utiliser des intervalles pour borner les dates. Exemple dans ton cas :

-- Jour en cours
SELECT MAX(Tempmax) AS Tempmax
FROM relevemeteo
WHERE DateTime
	BETWEEN CURDATE()
	AND CURDATE()+INTERVAL 1 DAY;

-- Mois en cours
SELECT MAX(Tempmax) AS Tempmax
FROM relevemeteo
WHERE DateTime
	BETWEEN CONCAT( YEAR(CURDATE()), '-', MONTH(CURDATE(), '-01' )
	AND LAST_DAY(CURDATE());
 
-- Année en cours
SELECT MAX(Tempmax) AS Tempmax
FROM relevemeteo
WHERE DateTime
	BETWEEN CONCAT( YEAR(CURDATE()), '-01-01' )
	AND CONCAT( YEAR(CURDATE())+1, '01-01' );

Hmmm je vais essayer d'être plus clair... Quand tu cherches un mot dans le dictionnaire, tu le trouves sans avoir à lire tout le dictionnaire, parce que les mots sont triés dans l'ordre.

Un index de BDD c'est comme un index de livre. Mettons que tu prends un livre d'histoire, tu cherches dans l'index à la fin...

- C'est trié donc la recherche est rapide

- Tu trouves que "Louis 14" est référencé aux pages 100, 101, 103, etc, tu vas donc regarder ces pages.

Un index de BDD fonctionne exactement de la même façon. Si tu cherches dans le dictionnaire tous les mots qui commencent par "bon", pas de problème : tu trouves le premier, et ils sont tous à la suite, donc tu les lis dans l'ordre, jusqu'à en trouver un qui ne commence pas par "bon", alors t'as fini..

Idem en BDD pour rechercher un datetime dans un intervalle :

DATE(DateTime) = '2016-10-01'

Là, toi tu sais que la fonction DATE() revient à dire "cherche tous les DateTime qui commencent par '2016-10-01'", c'est la même chose que l'exemple du dictionnaire, mais MySQL ne fait pas le lien, lui. L'optimiseur voit ça :

fonction(DateTime) = '2016-10-01'

Et pour lui, "fonction" ça pourait être n'importe quoi, pour reprendre l'exemple du dictionnaire, si ta fonction prend les 3 dernières lettres du mot, eh ben ton index type dictionnaire est inutile, il faut utiliser un dictionnaire de rimes à la place! Donc... il lit toutes les lignes, il applique la fonction, et il compare le résultat, et c'est super lent. Alors...

DateTime >= '2016-10-01 00:00:00' AND DateTime < '2016-10-02 00:00:00'

Tu vois c'est très con, dire qu'un DateTime est contenu dans un jour en particulier, correspond à dire qu'il est entre minuit et minuit du jour suivant. Ce n'est pas la peine de mettre l'heure d'ailleurs :

DateTime >= '2016-10-01' AND DateTime < '2016-10-02'

Si tu créé un index sur DateTime alors cette requête l'utilisera.

1) Regarde dans phpmyadmin si il ya un index (screenshot si besoin)

2) clique sur "créer index" sinon

D'autre part tu devrais créer une table "min/max par jour" pour chaque donnée, où tu compilerais toutes les infos à la fin de la journée. Il y aurait 24h*60min/5min = 288 fois moins de lignes dedans vu que tu as 288 mesures/jour !

Note poyr Benzouye: il faut pas utiliser BETWEEN, en effet :

dt BETWEEN '2016-01-01' AND '2016-01-02'

va sélectionner "2016-01-02 00:00:00' si il est présent, car BETWEEN opère sur un intervalle fermé. D'où le signe < strict (et non <=) dans ma condition...

-
Edité par Lord Casque Noir 28 octobre 2016 à 12:00:41

merci à tous les deux pour l'aide, je n'ai pas tout "encore" compris  ni résolus complètement mon soucis de temps mais je vais relire pas à pas les infos de casque noir pour y voir plus clair ! 

Dominique

Ennn avant ! Je créé une table à peu près identique à la tienne. Je la mets en InnoDB puisque ce sont les mesures originales, donc des données importantes, on ne va pas mettre ça en MyISAM... Et je la remplis avec 1M mesures aléatoires.

CREATE TABLE relevemeteo( 
    id          INT PRIMARY KEY AUTO_INCREMENT,
    datetime    DATETIME NOT NULL,
    temp               FLOAT NOT NULL,
    tempmax            FLOAT NOT NULL,
    tempmin            FLOAT NOT NULL,
    humidite           FLOAT NOT NULL,
    point_de_rosee     FLOAT NOT NULL,
    vitesse_vent       FLOAT NOT NULL,
    vitesse_rafale     FLOAT NOT NULL,
    direction_vent     FLOAT NOT NULL,
    
    key(datetime)
) ENGINE=InnoDB;

INSERT INTO relevemeteo 
SELECT id, DATE_ADD( '2000-01-01', INTERVAL 5*id MINUTE ),
rand(),rand(),rand(),rand(),rand(),rand(),rand(),rand() FROM seq;

Premier essai : J'ai la flemme avec toutes ces colonnes, je créé donc une view.

CREATE VIEW relflat AS
          SELECT datetime AS dt, "t"   AS name, temp           AS val FROM relevemeteo
UNION ALL SELECT datetime AS dt, "tma" AS name, tempmax        AS val FROM relevemeteo
UNION ALL SELECT datetime AS dt, "tmi" AS name, tempmin        AS val FROM relevemeteo
UNION ALL SELECT datetime AS dt, "hum" AS name, humidite       AS val FROM relevemeteo
UNION ALL SELECT datetime AS dt, "pr"  AS name, point_de_rosee AS val FROM relevemeteo
UNION ALL SELECT datetime AS dt, "vv"  AS name, vitesse_vent   AS val FROM relevemeteo
UNION ALL SELECT datetime AS dt, "vr"  AS name, vitesse_rafale AS val FROM relevemeteo
UNION ALL SELECT datetime AS dt, "dv"  AS name, direction_vent AS val FROM relevemeteo
;

Résultat : échec total car dans une requête type "SELECT * FROM relflat WHERE dt=constante" MySQL 5.5.44 est trop con pour propager la condition de WHERE à l'intérieur des UNION, il scanne donc toute la table 9 fois.

2è tentative : eh ben, allez on fait une table à la place ! MyISAM bien sûr puisque on s'en fout de perdre ces données là.

CREATE TABLE relflat
(
    dt      DATETIME NOT NULL,
    name    CHAR(3)  NOT NULL,
    value   FLOAT    NOT NULL,

    KEY( dt, name )
) ENGINE=MyISAM;

Pour remplir cette table, ou la maintenir à jour... Il suffit d'exécuter cette série de requêtes périodiquement, via le planificateur de tâches, ou dans un trigger post-insert sur relevemeteo, ou à la main... peu importe. Avec le million de lignes, le tout prend une minute environ ; bien sûr en mode mise à jour, seules les nouvelles lignes seront ajoutées, ce sera donc quasi instantané.

SELECT @lastdt := COALESCE(max(dt),'0000-00-00') FROM relflat;
INSERT INTO relflat SELECT datetime, "t"  , temp           FROM relevemeteo WHERE datetime > @lastdt;
INSERT INTO relflat SELECT datetime, "tma", tempmax        FROM relevemeteo WHERE datetime > @lastdt;
INSERT INTO relflat SELECT datetime, "tmi", tempmin        FROM relevemeteo WHERE datetime > @lastdt;
INSERT INTO relflat SELECT datetime, "hum", humidite       FROM relevemeteo WHERE datetime > @lastdt;
INSERT INTO relflat SELECT datetime, "pr" , point_de_rosee FROM relevemeteo WHERE datetime > @lastdt;
INSERT INTO relflat SELECT datetime, "vv" , vitesse_vent   FROM relevemeteo WHERE datetime > @lastdt;
INSERT INTO relflat SELECT datetime, "vr" , vitesse_rafale FROM relevemeteo WHERE datetime > @lastdt;
INSERT INTO relflat SELECT datetime, "dv" , direction_vent FROM relevemeteo WHERE datetime > @lastdt;

Alors, tu vas me demander pourquoi je me suis pris la tête à changer le format de la table... eh ben c'est parce que je pensais à l'origine pouvoir le faire avec une VIEW, mais ça a pas marché... et c'est la dernière fois qu'on touchera les colonnes une par une.

Maintenant, on créé une table des statistiques par jour: elle contiendra le min, max, et moyenne de chaque valeur, ainsi que l'heure de la dernière observation de ce maximum. Sauf pour la moyenne, où je mets l'heure à NULL... Note: j'utilise le "GROUP BY" un peu spécial de MySQL pour avoir l'heure du max, c'est plus rapide que de faire un JOIN.

CREATE TABLE reljour(
    d       DATE NOT NULL,
    name    VARCHAR(16) NOT NULL,
    value   FLOAT NOT NULL,
    t       TIME NULL,
    
    PRIMARY KEY( d, name )
) ENGINE=MyISAM;

SELECT @lastd := COALESCE(DATE(max(d)), '0000-00-00') FROM reljour;

INSERT INTO reljour (d, name, value, t) 
    SELECT d, CONCAT('min_',name), value, TIME(dt) AS t FROM (
        SELECT DATE(dt) AS d, dt, name, value FROM relflat 
        WHERE dt >= @lastd 
        ORDER BY d, name, value) AS foo 
    GROUP BY d, name
    UNION ALL 
    SELECT d, CONCAT('max_',name), value, TIME(dt) AS t FROM (
        SELECT DATE(dt) AS d, dt, name, value FROM relflat 
        WHERE dt >= @lastd 
        ORDER BY d, name, value DESC) AS foo 
    GROUP BY d, name
    UNION ALL
    SELECT DATE(dt), CONCAT('avg_',name), avg(value), NULL FROM relflat WHERE dt >= @lastd 
    GROUP BY DATE(dt), name
ON DUPLICATE KEY UPDATE value=VALUES(value), t=VALUES(t);

On a donc les stats par jour. Grâce au "ON DUPLICATE KEY UPDATE" les stats du jour en cours sont mises à jour aussi, à chaque fois qu'on exécute la requête...

Et donc pour afficher ta page :

mysql> SELECT * FROM reljour WHERE d='2009-08-01';
+------------+---------+-------------+----------+
| d          | name    | value       | t        |
+------------+---------+-------------+----------+
| 2009-08-01 | avg_dv  |     0.52038 | NULL     |
| 2009-08-01 | avg_hum |     0.49628 | NULL     |
| 2009-08-01 | avg_pr  |    0.505379 | NULL     |
| 2009-08-01 | avg_t   |       0.477 | NULL     |
| 2009-08-01 | avg_tma |    0.501037 | NULL     |
| 2009-08-01 | avg_tmi |     0.49085 | NULL     |
| 2009-08-01 | avg_vr  |    0.521353 | NULL     |
| 2009-08-01 | avg_vv  |    0.489442 | NULL     |
| 2009-08-01 | max_dv  |    0.995388 | 02:55:00 |
| 2009-08-01 | max_hum |    0.996309 | 19:05:00 |
| 2009-08-01 | max_pr  |      0.9928 | 08:10:00 |
| 2009-08-01 | max_t   |     0.98529 | 06:05:00 |
| 2009-08-01 | max_tma |    0.999798 | 16:40:00 |
| 2009-08-01 | max_tmi |    0.998219 | 13:40:00 |
| 2009-08-01 | max_vr  |    0.998745 | 13:25:00 |
| 2009-08-01 | max_vv  |    0.996862 | 21:35:00 |
| 2009-08-01 | min_dv  | 0.000291805 | 04:10:00 |
| 2009-08-01 | min_hum | 0.000420878 | 23:10:00 |
| 2009-08-01 | min_pr  |  0.00218924 | 19:25:00 |
| 2009-08-01 | min_t   |  0.00197513 | 14:30:00 |
| 2009-08-01 | min_tma |  0.00326699 | 13:05:00 |
| 2009-08-01 | min_tmi | 0.000500752 | 12:45:00 |
| 2009-08-01 | min_vr  |  0.00295576 | 03:35:00 |
| 2009-08-01 | min_vv  |  0.00317908 | 23:15:00 |
+------------+---------+-------------+----------+

Ce que ce charabia signifie : 2009-08-01 | max_hum |    0.996309 | 19:05:00 

c'est que le max d'humidité du 2009-08-01 est de 0.996309 (j'ai utilisé des données aléatoires) et s'est produit à 19:05 (j'aurais pu

Et maintenant, pour un mois... j'utilise le même principe que précédemment (avec le group by) pour récupérer le max et le min ainsi que le jour et l'heure où ça s'est produit...

-- met les variables @mbegin et @mend à la bonne valeur
SELECT @mbegin := '2009-08-01', @mend := DATE_ADD( @mbegin, INTERVAL 1 MONTH );

SELECT d, CONCAT('min_',name) AS name, value, t FROM 
    (SELECT * FROM reljour WHERE d >= @mbegin AND d < @mend ORDER BY name, value) AS foo 
    GROUP BY name
UNION ALL 
SELECT d, CONCAT('max_',name), value, t FROM 
    (SELECT * FROM reljour WHERE d >= @mbegin AND d < @mend ORDER BY name, value DESC) AS foo 
    GROUP BY name
UNION ALL
SELECT d, CONCAT('avg_',name), avg(value), NULL FROM reljour WHERE d >= @mbegin AND d < @mend
GROUP BY name;

La présentation des résultats est un peu déroutante (je n'inclus ici que la température, que j'ai nommée "t")

+------------+-------------+-------------------------+----------+
| d          | name        | value                   | t        |
+------------+-------------+-------------------------+----------+
| 2009-08-20 | min_avg_t   |     0.46379995346069336 | NULL     |
| 2009-08-01 | min_max_t   |       0.985289990901947 | 06:05:00 |
| 2009-08-30 | min_min_t   |   0.0003147907555103302 | 18:10:00 |
| 2009-08-10 | max_avg_t   |      0.5240919589996338 | NULL     |
| 2009-08-13 | max_max_t   |      0.9999893307685852 | 22:05:00 |
| 2009-08-09 | max_min_t   |    0.009109452366828918 | 13:45:00 |
| 2009-08-01 | avg_avg_t   |      0.5019449956955448 | NULL     |

Par exemple, "min_avg_t" c'est le minimum sur un mois de la moyenne sur une journée, donc "journée la plus froide"

"max_max_t" c'est le maximum du maximum ! donc... simplement le maximum.

"max_min_t" c'est le maximum du minimum ! Genre la nuit la plus chaude, par exemple.

Après, tu fourres ça dans un array php et tu affiches ce que tu veux. Tu vas jeter la majorité des résultats, mais bon, la requête prend moins de 10 ms... donc on s'en fout...

Note que le fait de mettre le nom de la donnée dans une colonne permet de traiter tous les min/max d'un coup et de ramener l'heure pù se produit le min/max en UNE requête ; alors que de laisser les données en colonnes comme dans la table originale nécessite une requête par colonne !... ou une jointure par colonne, ce qui est encore pire si il y a des ex aequo...

watchaw