CODEWITHSUNDEEP
mysqlsql
Vikrant Pradhan
Vikrant Pradhan

Reputation: 1

MySQL -> How to get the one row previous to a specific row

time value
00:00 100
00:01 101
00:02 102
01:03 103
02:04 104
03:05 105
03:10 106

Say, I have a mysql table with the above stucture indexed on the time column.

How can I get the first row just before a specific timestamp. For instance I should to be able to read the row of [02:04 : 104] if I want to get a timestamp before 02:30.

Also since the time doesn't increase consistently I cannot fetch by just decreasing by one minute.

One way I know of is by ordering rows by descending and limit by 1, but it seems this performs an index range scan which would be costly as the number of rows increases. Is there any way to get the value as constant time operation?

Upvotes: 0

Views: 87

Answers (2)

notiv
notiv

Reputation: 431

I don't think you can do it in constant time. I believe you should create the previous time point using LAG, add an index, then filter with a WHERE statement. Out of curiosity and since you have an index on time, does this run faster than the LIMIT query?

WITH cte AS (
   SELECT time,
      value,
      LAG(time) OVER(ORDER by time) AS prev_time
   FROM input_table
   -- Try to add a WHERE statement here to limit the number of rows selected (since you have an index on time). That would make things faster of course.
)
SELECT *
FROM cte
WHERE '02:30' BETWEEN prev_time and time

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You may use a LIMIT query:

SELECT time, value
FROM yourTable
WHERE time < '02:30'
ORDER BY time DESC
LIMIT 1;

The above query can benefit from the following index:

CREATE INDEX idx ON yourTable (time, value);

This should let MySQL rapidly find the record of interest, in logarithmic time, and return it. The index also covers the value column, so this can be said to be a covering index.

Upvotes: 1

Related Questions