Question 60870 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Getting Average number of latest dates

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

Tags:

2

Answers


  1. 0
    1. You can use
      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_s_id = (SELECT s2.l_s_id FROM sensorlogs AS s2 GROUP BY s2.l_s_id ORDER BY s2.l_s_id DESC LIMIT 1)
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search