I'm newish to MySQL and a lot of my query is pieced together from this site and trial and error, so any help is appreciated. If I left out anything, or more detail is needed, I will be happy to give what I can.
My query takes, on average, about 90 secs to run. It also doesn't return all the correct values. Even if speed can't be fixed, how do I correct it to return the proper values?
I'm trying to get the most recent set of temperatures for a given location and also an average for the last 24hrs. A location can have up to 20 thermometers.
SELECT t.Common_name, f.Food_name, e.Temperature, e.Temp_timestamp AS Time, Daily_avg.avg_temp FROM Thermometer t JOIN Temp_records e ON e.Temp_therm_id = t.Therm_id LEFT JOIN Food f ON f.Food_therm_id = t.Therm_id JOIN ( SELECT Temp_therm_id, AVG( Temperature ) AS avg_temp FROM Temp_records WHERE Temp_timestamp >= NOW( ) - INTERVAL 1 DAY GROUP BY Temp_therm_id ) AS Daily_avg USING ( Temp_therm_id ) WHERE t.Therm_unit_id =2 AND Temp_records_id IN ( SELECT MAX( Temp_records_id ) FROM Temp_records GROUP BY Temp_therm_id )
Therm_id Therm_unit_id Common_name
Temp_records Table (~18k records at the moment)
Temp_records_id Temp_therm_id Temp_food_id Temperature
Food_id Food_unit_id Food_name Food_therm_id
(The probe isn't in food, that's just the ambient temperature in C)
The db has slightly different temperatures for the given food items, and since I can only get the temperature of one food item at a time, they obviously can't have the same time stamp.
Should I remove the averaging part in to it's own query?
If I remove the food part of the query, it returns my 3 most recent temperatures and their averages. (I only have 3 thermometers for testing at the moment)