1

I have a table Chron containing the following columns:

ID | timestamp | ...

Some timestamps have been erroneously changed to a specific date D. I want to change them to the timestamp of the nearest rows (in terms of ID) which have not been changed. My query is:

UPDATE Chron as C1
SET C1.timestamp = (
                    SELECT C2.timestamp
                    FROM Chron as C2
                    WHERE C2.timestamp != D
                    AND NOT EXISTS (
                        SELECT *
                        FROM Chron as C3
                        WHERE ABS(C2.id - C1.id) >
                              ABS(C3.id - C1.id)
                    )
)
WHERE C1.timestamp = D

The problem is that my table contains more than 300K rows and this query would take a lot of time to run. How can I optimize it? I know that it is not necessary to compare a given row C1 with the whole table, but just the neighbourhood is sufficient, but the optimal size of the neighbourhood is not always the same.

  • Are you sure the subquery will always return a single row? Can you explain - with a small sample - what the UPDATE is going to do? – ypercubeᵀᴹ Jul 18 '18 at 9:35
  • Look at this fiddle, maybe there is something useful in it. – Akina Jul 18 '18 at 9:43
  • @Akina it looks very useful, can you explain it a bit? – firion Jul 18 '18 at 9:52
  • Records in any subquery (later - sq) are processed in sorting order set in it. 1st sq simply initializes user variable, 3rd sq calculates id of the closest valid record with lower id value, 4th sq do the same, but obtain the higher id value, outer query calculates which of them is the most closest. PS. 2nd sq is nesessary only in case when the first record processed in 3rd sq is not valid - if both first and last records are valid, you can remove 2nd (aliased as hh) sq from FROM section and its linking condition from WHERE section. – Akina Jul 18 '18 at 10:01
  • That is fiddle with editions described in previous comment's postscript. And, of course, you can re-write it from cartesian to joins. – Akina Jul 18 '18 at 10:07
1

Here I suppose that id is ordered chronologically just like timestamp field.

UPDATE Chron AS w
   SET w.timestamp = IF( w.timestamp = D, @ts, @ts := w.timestamp )
 ORDER BY w.id ASC
;

The only requirement is that first (by id) record should have the correct timestamp.

Also that query update the broken timestamp to the value of the previous correct/corrected row and if you have the long continuous run of rows with broken timestamps result will be different from the "closest value". Anyway the "closest value" need a complicated query so "previous value" is good enough if you need some fast solution.

|improve this answer|||||
  • Is it correct to say that the timestamp of a row is set to the last timestamp different from D? – firion Jul 18 '18 at 11:49
  • Yes it is. All timestamps equal to D will be eliminated. And if timestamp is not D it will be updated to itself (with @ts assinged to it too) – Kondybas Jul 18 '18 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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