Getting the newest record from a database Multiple queries
I have a mysql database with vehicles records. I need a fast query that will return the newest records of those records that were updated within the last 4 minutes. For example vehicle "A" may be updated several times a minute so it will appear many times within the last 4min. Same with vehicle B C etc. I need only the most recent entries for each vehicle within a 4 min window. I have tried like this
SELECT *
FROM yourtable AS a
WHERE a.ts =
(SELECT MAX(ts)
FROM yourtable AS b
WHERE b.ts > NOW() - INTERVAL 5 MINUTE
AND b.name = a.nam开发者_StackOverflow社区e)
but it takes too long to produce results >10seconds.
Does this work?
select distinct a.id from yourtable AS a
where a.ts > NOW() - INTERVAL 5 MINUTE;
It gives all vehicles that were updated in last 4/5 minutes.
Thanks, Rakesh
精彩评论