I have a PHP page for a weather station that I'm trying to build.
I run multiple queries to pull out data from a MySQL database for min/max/average values for temp, pressure, humidity etc.
All of these are done by multiple MySQL select statements - here are 2 of them:
$sql_dht22_temp_max = "select DATE_FORMAT(date,'%H:%i'), dht22_temp from weather inner join (select max(dht22_temp) as ts from weather where date(date) = curdate()) as ds on weather.dht22_temp = ds.ts order by date desc limit 1;";
if (!$result = $db->query($sql_dht22_temp_max)){
die('error 1');
}
while ($row = $result->fetch_assoc()){
$dht22_temp_max = $row['dht22_temp'];
$dht22_max_date = $row["DATE_FORMAT(date,'%H:%i')"];
}
$sql_inside_temp_min = "select DATE_FORMAT(date,'%H:%i'), temperature from inside_temp inner join (select min(temperature) as ts from inside_temp where date(date) = curdate()) as ds on inside_temp.temperature = ds.ts order by date desc limit 1;";
if(!$result = $db->query($sql_inside_temp_min)){
die('error running query');
}
while($row = $result->fetch_assoc()){
$inside_temp_min = $row['temperature'];
$inside_min_date = $row["DATE_FORMAT(date,'%H:%i')"];
}
While each statement takes a few milliseconds to run, adding them all up and it takes about 3-4 seconds. So on loading my page, you have to wait 3-4 seconds, which doesn't seem like much but is a pain.
If there a better way of doing this? Running all the statement in parallel? Can the MySQL/PHP code be optimized?
explain select DATE_FORMAT(date,'%H:%i') As MyDate, dht22_temp
from weather inner join (select min(dht22_temp) as ts from weather
where date(date) = curdate()) as ds on weather.dht22_temp = ds.ts
order by date desc limit 1;
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'PRIMARY', '<derived2>', 'system', NULL, NULL, NULL, NULL, '1', NULL
'1', 'PRIMARY', 'weather', 'index', NULL, 'id_date', '9', NULL, '1', 'Using where'
'2', 'DERIVED', 'weather', 'ALL', NULL, NULL, NULL, NULL, '65420', 'Using where'
Here is the table structure:
# Field, Type, Null, Key, Default, Extra
'idweather', 'int(11)', 'NO', 'PRI', NULL, 'auto_increment'
'date', 'datetime', 'YES', 'MUL', NULL, ''
'rainfall', 'float', 'YES', '', NULL, ''
'windspeed', 'float', 'YES', '', NULL, ''
'dewpoint', 'float', 'YES', '', NULL, ''
'pressure', 'float', 'YES', '', NULL, ''
'bmp085_temp', 'float', 'YES', '', NULL, ''
'dht22_temp', 'float', 'YES', '', NULL, ''
'abs_hum', 'float', 'YES', '', NULL, ''
'gust', 'float', 'YES', '', NULL, ''
'uvi', 'float', 'YES', '', NULL, ''
'light', 'float', 'YES', '', NULL, ''
'rel_humidity', 'float', 'YES', '', NULL, ''
'wind_dir', 'varchar(5)', 'YES', '', NULL, ''