Query performance low for aggregate function

Question

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"]]);

Answer 1

Let's see the entire SHOW CREATE TABLE; we need to see that you are missing INDEX(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 case VARCHAR is the wrong declaration.

• Andrew Brennan: How many records have unitid = 2000? You may be wasting your time trying to make this less cpu intensive. Perhaps find a better way to obtain this value.
• Rick James: Without INDEX(unitid), it must scan the entire table. Without the quotes it must scan the entire id -- because of converting the string to a number to check against 2000. A table scan is (usually) much slower than being able to use an index.