2

My energy-meter is writing the (accumulated) consumtion every minute to a db. In order to get my todays consumption I use:

SELECT
  (MAX(energy_kwh) - MIN(energy_kwh)) AS kwh_today
FROM logging.main_meter
WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE();

Problem is: max() and min() appear to slow the query down a lot. I added a key to column energy_kwh but it didn't help. I bet there is a better way to get the first and last record for the calculation.

The table (its actually much bigger, ~130 columns, so I reduced it to the relevant part):

CREATE TABLE 'main_meter` 
( 
  id int(11) NOT NULL AUTO_INCREMENT, 
  timestamp int(11) DEFAULT NULL, 
  strtime datetime DEFAULT NULL, 
  energy_kwh double unsigned DEFAULT '0', 
) ENGINE=InnoDB AUTO_INCREMENT=1717655 DEFAULT CHARSET=latin1;
Improve this question
6
  • Hi, and welcome to the forum! Could you please tell us what server (and version) you are running? – Vérace Jan 17 at 10:30
  • Hello, mysql -v outputs "Server version: 10.1.37-MariaDB mariadb.org binary distribution". Its part of a XAMPP package running on my MS Server 2019 Essentials – 32Smooth Jan 17 at 10:35
  • 2
    Please show the output of SHOW CREATE TABLE logging.main_meter\G! DATEs should be stored as DATEs and not as strings - this could be contributing to any slowdown - it's poor practice anyway! – Vérace Jan 17 at 10:43
  • the table is rather big. I used a simplified version to showcase the query, but here we go: CREATE TABLE main_meter` ( id int(11) NOT NULL AUTO_INCREMENT, timestamp int(11) DEFAULT NULL, strtime datetime DEFAULT NULL, energy_kwh double unsigned DEFAULT '0', ) ENGINE=InnoDB AUTO_INCREMENT=1717655 DEFAULT CHARSET=latin1` – 32Smooth Jan 17 at 10:46
  • you are right, its actually the date that slows it down, not the min()/max() ... hmm ok then I need to find a selection based on the timestamp value I gess – 32Smooth Jan 17 at 10:59
9

as it turned out, it wasn't the MIN()/MAX() functions that slowed the query down, but the

WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE()

statement. Since the table has a column with the unix-timestap, using this instead accelerates it a lot:

WHERE TIMESTAMP > UNIX_TIMESTAMP(CURDATE())

Query-time from ~3.6s down to ~0.02s. I call that a good improvement.

Improve this answer
2
  • 1
    Don't forget to mark your own answer as correct (when you can... 24hrs?) so that others with the same problem can find a solution - +1 for thinking outside the box! – Vérace Jan 17 at 12:42
  • 2
    You've correctly identified strtime as the problem, it should be indexed. However, WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE() and WHERE TIMESTAMP > UNIX_TIMESTAMP(CURDATE()) are not the same. The first checks if strtime is at the current date. The second checks if tiimestamp is after the current date. The real problem is using date_format means it can't use the index. – Schwern Jan 17 at 20:53
4

The problem is here.

WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE()

strtime needs to be indexed, and the query needs to be written such that MySQL can use the index. Making a function call like date_format on it means MySQL may not be able to use the index.

Instead, try date. Because date does a known conversion, MySQL may be able to use an index.

where date(strtime) = curdate()

Otherwise, eliminate non-deterministic function calls entirely.

where curdate() <= strtime and strtime < curdate() + interval 1 day;

We can't use where strtime between curdate() and curdate() + interval 1 day because that is inclusive an would include midnight on the next day.

Note that the timestamp column is redundant with strtime if they are meant to contain the same time. Instead, replace timestamp with unix_timstamp(strtime).

Improve this answer
3
  • Slighlty odd way of saying it. I would have flipped the first half of the interval check: where strtime >= curdate() and strtime < curdate() + interval 1 day still half-open interval, just easier to comprehend. – Charlieface Jan 18 at 1:02
  • 1
    @Charlieface It's deliberate to emulate the syntactically invalid but clear lower-bound <= value < upper-bound showing the value is between the bounds. – Schwern Jan 18 at 1:07
  • @Charlieface doing comparisons in number line order is a stylistic choice which many find easier to read – AakashM Jan 18 at 9:29
1
  1. switch the datetime comparision so u dont apply funciton on column but on curdate. Ie. Strtime between A and B.
  2. primary key on energy_kwh is not very usefull here i think. And risky. What if there is a timespan with 0 energy consumption. No change in value, PK violation.
  3. add index on strtime,, move dattime calculation to other side and it should be ok.
Improve this answer
1
  • I think I found a good solution now: SELECT (MAX(energy_kwh) - MIN(energy_kwh)) AS kwh_today FROM logging.main_meter WHERE TIMESTAMP > UNIX_TIMESTAMP(CURDATE()) Selection based on timestamp column is much faster – 32Smooth Jan 17 at 11:14
0

In addition to using the correct time format, instead of scooping up a whole day and finding the first and last records, why don't you search for (only) the record in the first minute of the day and (only) the record in the last minute of the day?

Improve this answer

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.