0

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

  • 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. – Andrew Brennan Oct 4 '16 at 16:24
  • 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. – Rick James Oct 4 '16 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.