MYSQL Query: latest timestamp + unique value from the last 30 minutes

Question

I need to retrieve the latest rows with a unique value from my mysql table. Simple table layout is timestamp (now()) and a username column. The table gets new data a couple of times a second, and i need the latest row where username is unique.

SELECT MAX(timestamp) as timestamp, username 
    FROM bla 
    WHERE timestamp < (now() - interval 30 minute) 
    GROUP BY username 
    ORDER BY timestamp DESC 

It seems that this query does not return the latest values, probably because the group is doing something i dont want...

Answer 1

If you're trying to look at the last 30 minutes, then I think you want to use "greater than" rather than "less than".

... WHERE timestamp > (now() - interval 30 minute) ...

Answer 2

While what you wrote does work, a faster solution might be to use a LEFT JOIN On the DB I'm working on LEFT JOIN is actually twice as fast w/ the added bonus that you get all the columns from the most recent rows if you want that info:

SELECT *
    FROM `bla`
    WHERE bla.created_at > (now() - interval 30 minute) AND (next_bla.created_at IS NULL)
    LEFT JOIN bla next_bla ON bla.username = next_bla.username AND bla.created_at < next_bla.created_at
    ORDER BY bla.created_at DESC

this matches every row in bla w/ the next row by timestamp w/ the same username and picks the rows that don't have a next row (next_bla.created_at IS NULL) i.e. they are the most recent rows.

You can also use a LIMIT clause to improve performance.

This is a good article that explains how GROUP BY works in detail and clear language: http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

Another similar answer by Andomar - MySQL "Group By" and "Order By"