CODEWITHSUNDEEP
mysqlsql
TheMonkWhoSoldHisCode
TheMonkWhoSoldHisCode

Reputation: 2332

SQL to return the latest record

One of the columns in my table has data type as TIMESTAMP. I would like get the latest record from the table using the timestamp column. The records in the table could vary between a few hundreds to millions. The query looks like this currently.

SELECT alarm_severity_id
FROM alarm_notification
WHERE alarm_life_cycle_id = 25
ORDER BY event_timestamp DESC
LIMIT 1;

Because of the number of records in the table, I suspect the query can take quite a bit of time (depending on the records). Of course I can put an index on the event_timestamp column to improve on performance. However, I am curious to know if this is the best query to get the results. Or is there any other PROPER way of doing it.

Upvotes: 1

Views: 113

Answers (2)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186833

You can try to find out the maximum timestamp and then the corresponding alarm_severity_id:

select alarm_severity_id
  from alarm_notification
 where (alarm_life_cycle_id = 25) and
       (event_timestamp in 
          (select Max(event_timestamp)
             from alarm_notification
            where alarm_life_cycle_id = 25))
 limit 1

Create an index on event_timestamp field (if you don't have one)

Upvotes: 1

Sharky
Sharky

Reputation: 6294

The way to go is to create one INDEX of two columns, first being the alarm_life_cycle_id and second the event_timestamp.

ALTER TABLE `alarm_notification` ADD INDEX `my_index` (`alarm_life_cycle_id`, `event_timestamp`)

With this index in place, the difference between MIN() MAX() and ORDER BY ... LIMIT 1 will be practically negligible please see more : https://stackoverflow.com/a/426785/953684

Upvotes: 2

Related Questions