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 > 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 > t1.time_recorded
and t2.device_id = 1154
limit 1)) as t2
from reading as t1
where t1.device_id = 1154;