How to efficiently determine changes between rows using SQL

I have a very large MySQL table containing data read from a number of sensors. Essentially, there's a time stamp and a value column. I'll omit the sensor id, indexes other details here:

CREATE TABLE `data` (
  `time` datetime NOT NULL,
  `value` float NOT NULL
)

The value column rarely changes, and I need to find the points in time when those changes occur. Suppose there's a value every minute, the following query returns exactly what I need:

SELECT d.*, 
  (SELECT value FROM data WHERE time<d.time ORDER by time DESC limit 1) 
    AS previous_value 
FROM data d 
HAVING d.value<>previous_value OR previous_value IS NULL;

+---------------------+-------+----------------+
| time                | value | previous_value |
+---------------------+-------+----------------+
| 2011-05-23 16:05:00 |     1 |           NULL |
| 2011-05-23 16:09:00 |     2 |              1 |
| 2011-05-23 16:11:00 |   2.5 |              2 |
+---------------------+-------+----------------+

The only problem is that this is very inefficient, mostly due to the dependent subquery. What would be the best way to optimize this using the tools that MySQL 5.1 has to offer?

One last constraint is that the values are not ordered before they are inserted into the data table and that they might be updated at a later point. This might affect any possible de-normalization strategies.

5
задан cg. 24 May 2011 в 11:46
поделиться