I perform a SUM query on a MySQL table of mine which takes around 1.5 seconds to complete. The table will have around 200000 rows at any time. I have to run this query every 5 seconds from my web app and because of that my DB CPU usage is shooting up to around 50%.
This is my query
select ((SUM(active_energy) *0.00277)/1000) as kwh from energy_readings WHERE unitid = 2000
I am using an Amazon RDS instance (db.m4.large). Is there an alternate way of writing this query with low server load?
My table structure is like this
Id bigint(20) AI PK unitid varchar(16) date_time timestamp active_energy float power_factor float CREATE TABLE energy_readings ( Id bigint(20) NOT NULL AUTO_INCREMENT, unitid varchar(16) DEFAULT NULL, date_time timestamp NULL DEFAULT NULL, active_energy float DEFAULT NULL, power_factor float DEFAULT NULL, PRIMARY KEY (Id) ) ENGINE=InnoDB AUTO_INCREMENT=6114324 DEFAULT CHARSET=latin1
I use this code to fetch the values. This works with string unitids as well; Should I be putting quotes around it?
$stmt = $db->prepare(" select ((SUM(active_energy) *0.00277)/1000) as kwh from energy_readings WHERE unitid=? "); $stmt->execute([$_SESSION["plug_id"]]);