Mysql – Get first and last record of a selection without using min() max()

MySQLquery-performance

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;

Best Answer

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.

Related Question