I have a table containing the temperature values from x number of sensors which report temperature changes. When a change API is received, the new temp is recorded and a cache is rebuilt for quick display to clients. But I keep running into timing issues (throttling) with this query taking too long.
SELECT t.unit,t.temperature,t.dateAdded FROM Device_y_Data t
WHERE t.deviceIndex=1 AND t.dateAdded=
(SELECT b.dateAdded FROM Device_y_Data b
WHERE b.deviceIndex=1 AND b.unit=t.unit
ORDER BY b.dateAdded DESC LIMIT 0,1)
ORDER BY t.unit
LIMIT 0, 30
Table structure:
id int(10)
dateAdded timestamp
deviceIndex tinyint(3)
unit tinyint(3)
temperature decimal(4,1)
Indexes:
Keyname Type Unique Packed ColumnComment
PRIMARY BTREE Yes No id
dateAdded BTREE No No dateAdded
deviceIndex BTREE No No deviceIndex
unit
The result is a list of the most recent temps for each unit:
unit temperature dateAdded
1 67.0 2014-03-26 10:28:11
2 66.0 2014-03-26 10:26:01
3 68.0 2014-01-11 15:41:24
4 67.0 2014-01-15 09:53:16
5 67.0 2014-01-11 10:36:17
The EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t ALL deviceIndex NULL NULL NULL 7035 Using where; Using filesort
2 DEPENDENT SUBQUERY b index deviceIndex dateAdded 4 NULL 1 Using where
In previous experiments I've tried MAX(), etc. Other answers here on stackoverflow pointed me to the use of LIMIT and ORDER BY which did improve performance. If I cannot figure out how to optimize this query, I guess I could programmatically query each unit's most recent temp.
The current database has 7000 rows. Not much! But the slowMySQL_logs show: Query_time: 19.673578 Lock_time: 0.000239 Rows_sent: 5 Rows_examined: 5900033. Seems like I could bring down the rows examined!
Thank you in advance for any suggestions.
