CODEWITHSUNDEEP
mysqlsqlselect
CrownJJ
CrownJJ

Reputation: 43

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

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?

Upvotes: 4

Views: 243

Answers (2)

Andomar
Andomar

Reputation: 238126

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.

Upvotes: 4

Neil Hampton
Neil Hampton

Reputation: 1883

SELECT p1.ApplyID, p1.ApplyDate, COUNT(*) as SESSIONS
FROM tb p1, tb p2
WHERE p2.ApplyDate >= p1.ApplyDate
AND UNIX_TIMESTAMP(p2.ApplyDate)- UNIX_TIMESTAMP(p1.ApplyDate) <= 5
ORDER BY p1.ApplyDate

Upvotes: 0

Related Questions