Optimisation pour un chargement plus rapide

[mikael2235]

Bonjour,

J'ai fais un site météo qui m'indique les différents relevés de la journée.
Mon site est en php et va chercher les infos dans une base MySQL.

Au début, il y avait peu d'enregistrements donc tout se passait bien.
Mais depuis un moment, je suis à environ 60 000 enregistrements, et soit les pages mettent un temps énorme à charger, ou alors elle ne se chargent pas du tout. Le navigateur arrête avant.

Je pense que soit la structure de ma/mes tables sont à améliorer, ou alors mes requêtes ne sont pas optimisés.

Voici la structure de mes 2 tables principales :
base météo :


table capteurs :


table releves :


exemple de requete pour afficher la température actuelle :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
		<?php
		// requete temp actuelle
		$sql = "SELECT valeur_mesure, timestamp_mesure FROM releves WHERE id_capteur='20' AND type_mesure='temp' ORDER BY timestamp_mesure DESC LIMIT 1";
		// envoi requete
		$req = mysql_query($sql) OR die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());
		// ON fait une boucle qui va faire un tour pour chaque enregistrement
		while($data = mysql_fetch_assoc($req))
			{
			// ON affiche les informations de l'enregistrement en cours
			$time = $data['timestamp_mesure'];
			echo '<b>'.$data['valeur_mesure'].' °C</b> à '.date("H:i", strtotime("$time + 1 hours")).' le '.date("d/m", strtotime("$time + 1 hours")).'';
			} 
		?>

exemple de requete pour afficher le min/max sur 24h :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
		<?php
		// requete min temperature sur les 24 dernieres heures
		$sql = "SELECT valeur_mesure, timestamp_mesure FROM releves WHERE id_capteur='20' AND type_mesure='temp' AND timestamp_mesure BETWEEN DATE_ADD(NOW(),INTERVAL -26 HOUR) AND NOW() ORDER BY valeur_mesure ASC LIMIT 1";
		// envoi requete
		$req = mysql_query($sql) OR die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());
		// ON fait une boucle qui va faire un tour pour chaque enregistrement
		while($data = mysql_fetch_assoc($req))
			{
			// ON affiche les informations de l'enregistrement en cours
			$time = $data['timestamp_mesure'];
			echo '<b><font color="#0000CC">'.$data['valeur_mesure'].' °C</font></b> à '.date("H:i", strtotime("$time + 1 hours")).' le '.date("d/m", strtotime("$time + 1 hours")).'';
			} 
		?>

Exemple de requete pour récuperer l'état de la batterie :

Code :

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
 
		<?php
		// requete etat batterie (dernier connu)
		$sql = "SELECT valeur_mesure FROM releves WHERE id_capteur='20' AND type_mesure='battery' ORDER BY valeur_mesure DESC LIMIT 1";
		// envoi requete
		$req = mysql_query($sql) OR die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());
		// ON fait une boucle qui va faire un tour pour chaque enregistrement
		while($data = mysql_fetch_assoc($req))
			{
			// ON affiche les informations de l'enregistrement en cours
			$bat = $data['valeur_mesure'];
				if( $bat <= 100 && $bat > 50 ) {
					echo '<img src="./batterie/full.png" alt="batterie" title="batterie" />';
				}
				if( $bat <= 50 && $bat > 20 ) {
					echo '<img src="./batterie/mid.png" alt="batterie" title="batterie" />';
				}
				if( $bat <= 20 && $bat > 5 ) {
					echo '<img src="./batterie/low.png" alt="batterie" title="batterie" />';
				}
				if( $bat <= 5 && $bat > 0 ) {
					echo '<img src="./batterie/lowred.png" alt="batterie" title="batterie" />';
				}
			} 
		?>

Je souhaiterai donc savoir ce que je peux améliorer pour que le chargement soit plus rapide. Je n'utilise pas les index dans mes tables, car je ne comprends pas trop le fonctionnement.
J'ai 9 sondes différentes, donc 9 requêtes pour valeur actuelle, 9 requêtes pour min, 9 requêtes pour max, 9 requêtes pour batterie.

Merci pour votre aide.

[CinePhil]

Pourquoi faire des boucles alors qu'il n'y a qu'une seule ligne récupérée ?

Quels index sont mis sur la table releves ?

[mikael2235]

Envoyé par CinePhil

Pourquoi faire des boucles alors qu'il n'y a qu'une seule ligne récupérée ?

Quels index sont mis sur la table releves ?

Merci pour ta réponse.

Pour les requetes, j'enleve la boucle, ce qui me donne ceci :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
		<?php
		// requete temp actuelle
		$sql = "SELECT valeur_mesure, timestamp_mesure FROM releves WHERE id_capteur='20' AND type_mesure='temp' ORDER BY timestamp_mesure DESC LIMIT 1";
		// envoi requete
		$req = mysql_query($sql) OR die('Erreur SQL !<br>'.$sql.'<br>'.mysql_error());
		// ON fait une boucle qui va faire un tour pour chaque enregistrement
		$data = mysql_fetch_assoc($req)
		// ON affiche les informations de l'enregistrement en cours
		$time = $data['timestamp_mesure'];
		echo '<b>'.$data['valeur_mesure'].' °C</b> à '.date("H:i", strtotime("$time + 1 hours")).' le '.date("d/m", strtotime("$time + 1 hours")).''; 
		?>

Pour les index, je n'en ai pas mis. Si j'ai bien compris, cela sert à accelerer les recherches dans des tables importantes. Dans mon cas, le plus pertinent serait d'en mettre sur id_capteur et type_mesure ?

[CinePhil]

Pour les index, je n'en ai pas mis. Si j'ai bien compris, cela sert à accelerer les recherches dans des tables importantes. Dans mon cas, le plus pertinent serait d'en mettre sur id_capteur et type_mesure ?

Oui !
A minima, n'as-tu pas déjà une PRIMARY KEY (clé primaire in french) dans la table ?
La colonne id_capteur représentant une clé étrangère référençant l'identifiant du capteur, elle devrait aussi être indexée.
Et type_mesure, puisque c'est un critère de recherche fréquent, devrait aussi être indexé.

Pour plus d'info : Que faut-il indexer ?

[mikael2235]

99 secondes, pour définir un index sur type_mesure
215 secondes, pour définir un index sur id_capteur.

C'est normal ?

[CinePhil]

Ça fait peut-être un peu beaucoup vu la faible taille de la table mais maintenant que la table est indexée, les requêtes vont plus vite ?