Obtaining weather station data from a MySQL database

Question

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, ''

Answer 1

You SQL commands seem rather overelaborated. Let me first rewrite it in a more readable way:

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 I understand it correctly it seems you want to know at what time today the maximum temperate was reached and what that temperature was. You use the table weather with date and dht22_temp columns. In contrast to what you might think, I would start by splitting this command in two parts:

SELECT idweather, dht22_temp
FROM weather
WHERE date(date) = curdate()
ORDER BY dht22_temp DESC
LIMIT 1

This will execute quite fast and return the maximum temperature today, and the row where that maximum occured. Please make sure dht22_temp is properly indexed. Also note that this assumes that there's only 1 maximum. This might not be correct, and you can check that by reading more rows (increase LIMIT). Now you can check all kind of stuff that happened when the temperature was at its maximum:

SELECT *
FROM weather
WHERE idweather = <id of max temp>

This is very simple, but it gives you all the weather parameters when the temperature was at its maximum, including the time at which it occured. A lot of data in two simple steps.

Don't overcomplicate your SQL. But the main point is: Use your auto increment index!

I am, of course, making a point here. Once you've seen that we can rewrite the two SQL commands into a single one, reducing it to what was needed:

SELECT DATE_FORMAT(date,'%H:%i') as time, dht22_temp
FROM weather
WHERE date(date) = curdate()
ORDER BY dht22_temp DESC
LIMIT 1    

And doesn't that look much simpler? In your case however I would just grab the data for one whole day:

SELECT *
FROM weather
WHERE date(date) = curdate()

and read it in an array. Then do the manipulation, like sorting, in memory. You could even make a histogram this way, without having to access the database again.