mysql - Calculate time between records? -

i have table of events datestamps, , i'd display time interval between events. example, if had

event  |  datestamp -------------------      |   05:00   b    |   07:00   c    |   08:00   d    |   12:00  ... 

i'd write simple query gives me

event  |  datestamp  |  last | since_last -----------------------------------------      |   05:00     |  null |  null   b    |   07:00     |     |  2 hours   c    |   08:00     |   b   |  1 hour   d    |   12:00     |   c   |  4 hours 

the format of last column unimportant -- can milliseconds, seconds, human readable, whatever's easy. note actual data has proper datetimes, i'm lazy type date out.

the important part there nothing in record "b" points "a" immediately-previous event, solution in question doesn't work -- need syntax select records joined recent older record.

what i've tried looks like

select new.event, new.datestamp, old.event last,     timestampdiff(second, max(old.datestamp), new.datestamp) since_last events new, events old new.datestamp > old.datestamp limit 10; 

but appears hang -- let run on minute. per this question, tried

select event, datestamp,   (select max(old.datestamp)    events old    new.datestamp > old.datestamp   ) last_ds events new limit 10; 

this works takes on 14 seconds on table million events, though i'm selecting 10 records, must wrong.

try use

select event,datestamp,        @prevevent last,          timestampdiff(second, @prevdate, datestamp) since_last,        @prevdate:=datestamp,        @prevevent:=event events, (select @prevdate:=null,@prevevent:=null) t order datestamp 

sqlfiddle demo

to select last 10 events use subquery limit instead of events table.

select event,datestamp,        @prevevent last,          timestampdiff(second, @prevdate, datestamp) since_last,        @prevdate:=datestamp,        @prevevent:=event ( select *        events        order datestamp desc        limit 10      ) t1, (select @prevdate:=null,@prevevent:=null) t order datestamp 

upd:

to right first row (not null since_last field) in 10 recent should first limit 11 (10+1) in inner subquery , in outer query limit 10 offset 1.

select event,datestamp,last,since_last ( select event,datestamp,        @prevevent last,          timestampdiff(second, @prevdate, datestamp) since_last,        @prevdate:=datestamp,        @prevevent:=event ( select *        events        order datestamp desc        limit 11      ) t1, (select @prevdate:=null,@prevevent:=null) t order datestamp ) t3  limit 10 offset 1