sql - MySQL - turning data points into ranges -

i have database of measurements indicate sensor, reading, , timestamp reading taken. measurements recorded when there's change. want generate result set shows range each sensor reading particular measurement.

the timestamps in milliseconds i'm outputting result in seconds.

here's table:

create table `raw_metric` (     `row_id` bigint not null auto_increment,     `sensor_id` binary(6) not null,     `timestamp` bigint not null,     `angle` float not null,     primary key (`row_id`) ) 

right i'm getting results want using subquery, it's slow when there's lot of datapoints:

select row_id,     hex(sensor_id),     angle,     (         coalesce((                 select min(`timestamp`)                 raw_metric rm2                 rm2.`timestamp` > rm1.`timestamp`                     , rm2.sensor_id = rm1.sensor_id                 ), unix_timestamp() * 1000) - `timestamp`         ) / 1000 duration raw_metric rm1 

essentially, range, need next reading (or use current time if there isn't reading). subquery finds minimum timestamp later current 1 same sensor.

this query isn't going occur i'd prefer not have add index on timestamp column , slow down inserts. hoping might have suggestion alternate way of doing this.

update: row_id's should incremented along timestamps can't guaranteed due network latency issues. so, it's possible entry lower row_id comes occurs after later row_id, though unlikely.

this perhaps more appropriate comment solution, long comment.

you trying implement lead() function in mysql, , mysql not, unfortunately, have window functions. switch oracle, db2, postgres, sql server 2012 , use built-in (and optimized) functionality there. ok, may not realistic.

so, given data structure need either correlated subquery or non-equijoin (actually partial equi-join because there match on sensor_id). these going expensive operations, unless add index. unless adding measurements tens of times per second, additional overhead on index should not big deal.

you change data structure. if had "sensor counter" sequential number enumerating readings, use equijoin (although performance might still want index). adding in table require having trigger -- , perform worse index when inserting.

if have handful of sensors, create separate table each one. oh, can feel groans @ suggestion. but, if did, auto-incremented id perform same role. honest, if count number of sensors on each hand.

in end, might suggest take hit during insertion , have "effective" , "end' times on each record (as index on sensor id , either timestamp or id). these additional columns, find more uses table.

if doing 1 sensor, create temoprary table information , use auto-incremented id column. insert data it:

insert temp_rawmetric (orig_row_id, sensor_id, timestamp, angle)     select orig_row_id, sensor_id, timestamp, angle     raw_metric     order sensor_id, timestamp; 

be sure table has temp_rawmetric_id column auto-incremented , primary key (creates index automatically). order by makes sure incremented according timestamp.

then can query as:

select trm.sensor_id, trm.angle,        trm.timestamp starttime, trmnext.timestamp endtime temp_rawmetric trm left outer join      temp_rawmetric trmnext      on trmnext.temp_rawmetric_id = trm.temp_rawmetric_id+1; 

this require pass through original data data, , primary key join on temporary table. first might take time. second should pretty quick.