Select rows less than 5 minutes in size using DATE_SUB

Question

I have a table that receives hundreds of queries per minute. The problem I'm experiencing is that I only need to select the rows that were inserted in the last 5 minutes. I try this:

SELECT count(id) as count, field1, field2 FROM table WHERE timestamp > DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY timestamp DESC 

My problem is that it returns 70k + results and counting. I'm not sure what it is that I am doing wrong, but I would like to help with this. Also, if there was a way to group them by minute, so that they look like this:

 | count | field1 | field2 | ---------------------------- 

I like the help and guidance on this, so please let me know your thoughts.

Answer 1

You really don't need DATE_ADD/DATE_SUB , date arithmetic is much simpler:

 SELECT COUNT(id), DATE_FORMAT(`timestamp`, '%Y-%m-%d %H:%i') FROM `table` WHERE `timestamp` >= CURRENT_TIMESTAMP - INTERVAL 5 MINUTE GROUP BY 2 ORDER BY 2 

Answer 2

It seems like this will work, which is very close to what you had:

 SELECT MINUTE(date_field) as `minute`, count(id) as count FROM table WHERE date_field > date_sub(now(), interval 5 minute) GROUP BY MINUTE(date_field) ORDER BY MINUTE(date_field); 

Pay attention to the added column to show the minute, and the GROUP BY clause, which collects the results at the corresponding minute. Imagine that you had 5 small buckets marked in the last 5 minutes. Now imagine that you threw every line that was 4 minutes into her own bucket. count () will count the number of records found in each bucket. This is a brief visualization of how GROUP BY works. http://www.tizag.com/mysqlTutorial/mysqlgroupby.php seems like a worthy entry in GROUP BY if you need more information.

If you run this and the number of entries per minute seems too high, you will need to fix some problems. Try replacing COUNT (id) with MAX (date_field) and MIN (date_field) so you can understand what dates it captures. If MIN () and MAX () are in range, you may have more data written to your database than you understand.

You can also check that you do not have dates in the future, since all of them will now be> (). The MIN () / MAX () checks mentioned above should also identify this if this is a problem.