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;