MySQL - count how many rows in next 5 seconds for each record

mysql, select, sql

Solution

Sometimes a `join` outperforms a subquery. You could try:

select  p1.ApplyID
,       p1.ApplyDate,
,       count(*)
from    tb p1
join    tb p2
on      p2.ApplyDate between p1.ApplyDate and p1.ApplyDate + interval 5 second
group by
        p1.ApplyID
,       p1.ApplyDate
order by
        p1.ApplyDate

Creating an index on `ApplyDate` might help:

create index IX_TB_ApplyDate on tb (ApplyDate, ApplyID)

Two notes. Because you only select `ApplyID` and `ApplyDate`, this index is even covering for your query. And make sure your query doesn't use `UNIX_TIMESTAMP`, which may prevent MySQL from using the index.

Problem

I have a table `tb`: ``` ApplyID, ApplyDate, ================================= John, 2008-01-23 12:00:01 Joe, 2008-01-23 12:00:02 Mary, 2008-01-23 12:00:02 Snoopy, 2008-01-23 12:00:06 Snoopy, 2008-01-23 12:00:07 Snoopy, 2008-01-23 12:00:11 John, 2008-01-23 12:00:21 ``` I want to count how many rows in next 5 seconds for each row. Output like: ``` ApplyID, ApplyDate, Sessions ================================= John, 2008-01-23 12:00:01, 3 Joe, 2008-01-23 12:00:02, 4 Mary, 2008-01-23 12:00:02, 4 Snoopy, 2008-01-23 12:00:06, 3 Snoopy, 2008-01-23 12:00:07, 2 Snoopy, 2008-01-23 12:00:11, 1 John, 2008-01-23 12:00:21, 1 ``` The query I use: ``` SELECT p1.ApplyID, p1.ApplyDate, ( SELECT COUNT(*) FROM tb p2 WHERE p2.ApplyDate >= p1.ApplyDate AND UNIX_TIMESTAMP(p2.ApplyDate)- UNIX_TIMESTAMP(p1.ApplyDate) <= 5 ) AS sessions FROM tb p1 ORDER BY ApplyDate ``` It works but will take a long time to show result. Any better way to increase query performance?

Original source