1

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;

1 Answer 1

1

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
6
  • so far, so good. Can you point me to documentation for var_init_query? Google isn't helping. Nov 21, 2014 at 14:39
  • var_init_query is just an alias for the subquery. Here's the documentation for variables in MySQL: dev.mysql.com/doc/refman/5.1/en/user-variables.html
    – tombom
    Nov 21, 2014 at 14:58
  • Ah - got it. I didn't recognize it as an alias because it wasn't used anywhere else (and didn't have an 'AS'). (SELECT @prev := null) AS foo would also work. Nov 21, 2014 at 16:21
  • Exactly. I always forget AS because it's not mandatory and I'm a lazy typer.
    – tombom
    Nov 21, 2014 at 16:22
  • A subquery is not exactly a table. It's something "virtual". So how do you reference something you just created and that is not something "solid"? You make it "solid" by giving it a name. Imagine I would have a subquery like (SELECT 1 AS one). When the subquery has no name, how can you distinguish, if one is a column in your table or in your subquery when you select it? You can't. Therefore an alias is needed.
    – tombom
    Nov 21, 2014 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.