Taking the difference in timestamps between successive entries This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question.  If this is not an appropriate question for DBA, let me know and I'll skulk away quietly...

I'm trying to get the difference between timestamps using a self join.  Despite indexing, it seems to be taking a very long time.  I'd welcome suggestions for optimizations.

The setup (with sample data)

CREATE TABLE reading(reading_id INT, device_id INT, time_recorded TIMESTAMP);

CREATE INDEX my_idx on reading(device_id, time_recorded);

INSERT INTO reading(reading_id,device_id,time_recorded) VALUES
(42271930,1154,'2014-11-18 13:12:38'),
(42271931,1154,'2014-11-18 13:12:40'),
(42271932,1154,'2014-11-18 13:12:52'),
(42271933,1154,'2014-11-18 13:13:21'),
(42271934,1154,'2014-11-18 13:13:22'),
(42271935,1154,'2014-11-18 13:13:23'),
... snip ...


The query

    select t1.time_recorded as tr1,
           min(t2.time_recorded) as tr2,
           timestampdiff(second, t1.time_recorded, min(t2.time_recorded)) as td
      from reading as t1
inner join reading as t2
        on t2.time_recorded &gt; t1.time_recorded
     where t1.device_id = 1154
       and t2.device_id = 1154
  group by t1.time_recorded;


The explain

|ID |SELECT_TYPE|TABLE|TYPE|POSSIBLE_KEYS|KEY   |KEY_LEN |REF  |ROWS |FILTERED |EXTRA              |
|1  |SIMPLE     |t1   |ref |my_idx       |my_idx|5       |const|14   |100 |Using where; Using index|
|1  |SIMPLE     |t2   |ref |my_idx       |my_idx|5       |const|14   |100 |Using where; Using index|


An excerpt of the results

|                             TR1 |                             TR2 | TD |
|---------------------------------|---------------------------------|----|
| November, 18 2014 13:12:38+0000 | November, 18 2014 13:12:40+0000 |  2 |
| November, 18 2014 13:12:40+0000 | November, 18 2014 13:12:52+0000 | 12 |
| November, 18 2014 13:12:52+0000 | November, 18 2014 13:13:21+0000 | 29 |
| November, 18 2014 13:13:21+0000 | November, 18 2014 13:13:22+0000 |  1 |
| November, 18 2014 13:13:22+0000 | November, 18 2014 13:13:23+0000 |  1 |
| snip...


The question

The results are correct.  And on the small dataset given here, it runs in 170 ms (on the sqlfiddle site).  But on a table with ~20K rows, the query takes over seven minutes to run (on an Amazon RDS / AWS instance).

My feeble understanding of EXPLAIN suggests that the query is using the index properly.  Is this just an inherently slow query, or is there a faster way?

Update 1: A faster way

The following query is faster: about 2.5 minutes vs 7.  It assumes that timestamps are in increasing order -- while this is generally true it's not guaranteed, so I'm not particularly keen on this as a solution:

select t1.time_recorded as tr1,
       timestampdiff(second, 
                     t1.time_recorded, 
                     (select t2.time_recorded
                        from reading as t2
                       where t2.time_recorded &gt; t1.time_recorded
                         and t2.device_id = 1154
                       limit 1)) as t2
  from reading as t1
 where t1.device_id = 1154;
You don't need a self join of any kind. Just use variables.

SELECT
time_recorded AS current,
@prev AS previous,
timestampdiff(second, time_recorded, @prev) AS diff_between_current_and_previous,
@prev := time_recorded
FROM
reading
, (SELECT @prev := null) var_init_subquery
WHERE device_id = 1154
ORDER BY time_recorded


What's important in this query is first the ORDER BY and then the ordering in the SELECT clause.

@prev AS previous, /*here it outputs the value of the previous row*/
@prev := time_recorded /*here the value of the current row is assigned*/


The subquery in the FROM clause is just a fancy way to not have to write "two queries" like this:

SET @prev := NULL;
SELECT
time_recorded AS current,
@prev AS previous,
timestampdiff(second, time_recorded, @prev) AS diff_between_current_and_previous,
@prev := time_recorded
FROM
reading
WHERE device_id = 1154
ORDER BY time_recorded