1

I need to get a list of IDs ordered by datetime from a very large table with a few million rows. This select gives a result almost instantly (a result of aprox 300 rows).

SELECT t.ID,t.Datetime from table t where (...) ORDER BY t.Datetime

As an extra field I also need the time difference of the "Datetime" field in this row and the previous row. I want to identify rows with a high time difference.

I tried joining the table with itself. The performance of these joins is very bad. What is the best approach to compare rows within a search result only?

576i
  • 7,579
  • 12
  • 55
  • 92
  • 1
    You might get some ideas from [this](http://stackoverflow.com/questions/13196190/mysql-subtracting-value-from-previous-row-group-by) answer. It shows how to compare each row to the previous using variables. – ethrbunny Jan 05 '15 at 01:25

1 Answers1

1

The best way is using variables in this case. The following is basically the logic that you want:

SELECT t.*, @prevdt as prev_datetime, @prevdt := t.DateTime
FROM (SELECT t.ID, t.Datetime
      FROM table t
      WHERE (...)
      ORDER BY t.Datetime
     ) t CROSS JOIN
     (SELECT @prevdt := NULL) vars;

However, it is better to put all variable assignments in a single statement (MySQL does not guarantee the order of evaluation of expressions in a select):

SELECT t.*,
       if(@prevdt := @dt, if(@dt := t.DateTime, @prevdt, @prevdt),
          if(@dt := t.DateTime, @prevdt, @prevdt)) as prev_datetime
FROM (SELECT t.ID, t.Datetime
      FROM table t
      WHERE (...)
      ORDER BY t.Datetime
     ) t CROSS JOIN
     (SELECT @prevdt := NULL, @dt := NULL) vars;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks. This is what I was looking for. I understand the first block and got it to work. The second block returns NULL in prev_datetime for every row and I haven't fully understood how the nested IFs work. From reading it should return `@prevdt` in every case and assign t.Datetime to `@dt` every time and assign that to `@prevdt`. – 576i Jan 05 '15 at 18:06