MySQL Performance – Improving Query Performance for Aggregate Functions

MySQLperformancequery-performance

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 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.