0

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
)

Thermometer Table

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 Table

Food_id
Food_unit_id
Food_name
Food_therm_id   

What I'm currently seeing:
Query Output

(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)

0

Start by changing IN ( SELECT max... ) to = ( SELECT max... ).

Change f.Food_name to

( SELECT Food_name FROM Food WHERE Food_therm_id = t.Therm_id ) AS Food_name

and get rid of the LEFT JOIN Food ... (I am unsure whether the LEFT JOIN before JOIN is causing trouble.)

If those suggestions do not speed it up enough, then...

Please provide EXPLAIN SELECT ... so we can see how the query is executed.

What table is Temp_records_id in? Perhaps e? (Please qualify all column names.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.