MySQL speed up aggregation query on table

Question

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?

• a_vlad: 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
• Lennart - Slava Ukraini: Do you meterreadings that's not part of devises, i.e. what is the purpose of WHERE meterid IN (SELECT meterid from devices)?

Answer 1

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;

Answer 2

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)