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
Columns:
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"]]);
Best Answer
Let's see the entire
SHOW CREATE TABLE
; we need to see that you are missingINDEX(unitid)
.Then, when it still does not run any faster, we need to discuss whether
unitid
is really a string, in which case you should quote'2000'
or is really a number, in which caseVARCHAR
is the wrong declaration.