1

DB: Amazon RDS, OS: Linux, 2 vCPU, Memory: 4GB

I have a table with almost 10M rows of data. Below is the table structure:

CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Another table which stores device IDs (around 120 rows of data)

CREATE TABLE `devices` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`meterid` varchar(16) DEFAULT NULL,
`location` varchar(8) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `meterid_UNIQUE` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I need to get 15 min aggregated data for all 'meter ids' within a time frame. I use this query -

SELECT AVG(metervalue) as value
     , DATE_FORMAT(date_time, "%d %b %Y %H:%i") as label 
FROM meterreadings 
WHERE meterid IN (SELECT meterid from devices) 
  AND date_time BETWEEN '2018-06-23' AND '2018-06-24' 
GROUP BY DATE(date_time), HOUR(date_time), MINUTE(date_time) DIV 15 
ORDER BY date_time ASC;

This takes almost around 30 to 50 seconds to execute.

EXPLAIN on this query returned me this:

'1', 'SIMPLE', 'devices', 'index', 'meterid_UNIQUE', 'meterid_UNIQUE', '19', NULL, '125', 'Using where; Using index; Using temporary; Using filesort'

'1', 'SIMPLE', 'meterreadings', 'ref', 'meterid', 'meterid', '19', 'devices.meterid', '308', 'Using where'

I have 'Id' as the primary key in meterreadings table and there is an index on 'meterid' as well. How can I improve my query speed?

2
  • generally - all as expected, with GROUP BY DATE(date_time), HOUR(date_time), MINUTE(date_time) DIV 15 - You not use indexes for date time , plus try to change IN for INNER JOIN, may be help
    – a_vlad
    Jun 25, 2018 at 8:33
  • Do you meterreadings that's not part of devises, i.e. what is the purpose of WHERE meterid IN (SELECT meterid from devices)? Jun 25, 2018 at 8:48

2 Answers 2

2

I think the best thing to do is create a generated column and use that as your index. Consider this:

 CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 `fifteen_mins_date` datetime AS 
    (concat(DATE_FORMAT(date_time, 'Y-m-d H:'), 
            15*(minute(date_time) div 15), 
            ':00')) STORED,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`),
 KEY `fifteen_mins_date` (`fifteen_mins_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Then, your query could look like this:

SELECT AVG(metervalue) as value, DATE_FORMAT(min(date_time), "%d %b %Y %H:%i") as label 
FROM meterreadings WHERE meterid IN (SELECT meterid from devices) 
   AND fifteen_mins_date BETWEEN '2018-06-23' AND '2018-06-24' 
GROUP BY `fifteen_mins_date` 
ORDER BY min(date_time) ASC;
0
SELECT  FROM_UNIXTIME(UNIX_TIMESTAMP(date_time DIV (15*60))
                    * (15*60)) AS 'label',
        AVG(metervalue) AS 'value'
    FROM  meterreadings
    WHERE  date_time >= '2018-06-23'
      AND  date_time <  '2018-06-23' + INTERNAL 1 DAY
    GROUP BY  1
    ORDER BY  1;

And have INDEX(date_time)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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