MySQL speed up aggregation query on table 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?
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;