MySQL Getting Average number of latest dates

Question

Trying to get an average of number from a table based on the latest dates of each. It works, but really slow, 30 seconds. Main table has 5,782 rows, and is growing. Anyway to change this query and make it a bit faster?

SELECT AVG(l_temp) AS temp, AVG(l_hum) AS hum, MAX(l_timestamp) AS stamp FROM sensorlogs AS s1
    LEFT JOIN sensors s
      ON (s.s_id = s1.l_s_id)
    WHERE s.s_average = 1 AND s1.l_timestamp = (SELECT MAX(s2.l_timestamp) FROM sensorlogs AS s2 WHERE s2.l_s_id = s1.l_s_id)

Here is explain and my table:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	s	(NULL)	ALL	PRIMARY	(NULL)	(NULL)	(NULL)	2	50.00	Using where
1	PRIMARY	s1	(NULL)	ALL	(NULL)	(NULL)	(NULL)	(NULL)	5782	10.00	Using where; Using join buffer (Block Nested Loop)
2	DEPENDENT SUBQUERY	s2	(NULL)	ALL	(NULL)	(NULL)	(NULL)	(NULL)	5782	10.00	Using where

Tables:

CREATE TABLE `sensorlogs` (
  `l_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `l_s_id` int(11) NOT NULL,
  `l_temp` decimal(20,1) NOT NULL,
  `l_hum` decimal(20,1) NOT NULL,
  KEY `l_timestamp` (`l_timestamp`),
  KEY `l_s_id` (`l_s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `sensors` (
  `s_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_ident` varchar(500) DEFAULT NULL,
  `s_created` date NOT NULL,
  `s_average` int(11) NOT NULL DEFAULT '0',
  `s_alert` int(11) NOT NULL DEFAULT '0',
  `s_thresh` int(11) NOT NULL DEFAULT '0',
  `s_l_id` int(11) NOT NULL,
  `s_location` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Version: 5.7.19-0ubuntu0.16.04.1

Answer 1

Give this a try:

SELECT AVG(l_temp) AS temp, AVG(l_hum) AS hum, MAX(l_timestamp) AS stamp
FROM (
    SELECT l_s_id, MAX(l_timestamp) AS max_ts
    FROM sensorlogs
    GROUP BY l_s_id
) t
JOIN sensorlogs sl ON sl.l_s_id = t.l_s_id AND sl.l_timestamp = t.max_ts
WHERE EXISTS (SELECT 1 FROM sensors WHERE s_average = 1 AND s_id = t.l_s_id);

It will still need the composite index on (`l_s_id`, `l_timestamp`). As you do not have a PK on sensorlogs, you could make that index PK.