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 (
 unitid varchar(16) DEFAULT NULL,
 date_time timestamp NULL DEFAULT NULL,
 active_energy float DEFAULT NULL,
 power_factor float DEFAULT NULL,

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=?

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

  • 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. Oct 4, 2016 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, 2016 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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