Mysql – Process sessions foreach minute

innodbMySQL

I have a table that stores sessions. The columns are: ID, Start (Timestamp) and Stop (Timestamp, can be NULL).

It's for online radio. A listener connects to the stream, and a row is created. Once they stop listening, Stop is updated to the current time.

Currently, it's about 80K rows per day.

I'd like to process the sessions once a day by determining the max amount of listeners for each minute, and push that data into a different table: Timestamp, ListenerCount (UNSIGNED SMALL INT).

I'm not super concerned with speed, just with working out how I can process this data within MySQL. I'm assuming I use a stored procedure (not much experience there), loop over each minute between the MIN(Start) and Max(Stop), and do a COUNT() between x-minute and x-minute:59.

Does this sound viable? Is there a better way?

Thanks for your time.

Best Answer

Here's an idea you could build on to achieve this: create a simple table that contains only a list of integers from 1 to 24*60 (i.e. number of minutes in a day). Use this to generate the list of minutes in the day you're interested in. Then join that on your session data, grouping by "minute" to get a count of active sessions.

Probably better explained with an example:

Tables:

-- Holds a list of integers
create table minutes (m int) engine innodb;
-- The session data
create table sessions (id int,
                       st timestamp,
                       en timestamp) engine innodb;

Generate the list of "minutes"

delimiter ;;
create procedure fill_minutes()
begin
  declare i int default 0;
  while i < 60*24 do
    insert into minutes(m) values (i);
    set i = i + 1;
  end while;
end;;
delimiter ;

start transaction;
call fill_minutes();
commit;

Adapted from Generating a range of numbers in MySQL

To generate the minutes in a given day:

select
    date('2013-01-01 00:00:00') + interval m minute
from
    minutes;

2013-01-01 00:00:00
2013-01-01 00:01:00
2013-01-01 00:02:00
2013-01-01 00:03:00
2013-01-01 00:04:00
2013-01-01 00:05:00
....

A bit of fake data:

insert into sessions (id, st, en)
values (1, '2013-01-01 00:00:00', '2013-01-01 00:10:00')
,      (2, '2013-01-01 00:05:00', '2013-01-01 00:10:00')
,      (3, '2013-01-01 00:07:00', '2013-01-01 00:12:00');

Join the minute table with the session data

select
    dates.d, count(*)
from
    (select date('2013-01-01 00:00:00') + interval m minute d from minutes) dates
    join sessions s on ((dates.d >= s.st) and (dates.d < s.en))
group by dates.d
order by dates.d;

d                   count(*)
2013-01-01 00:00:00 1
2013-01-01 00:01:00 1
2013-01-01 00:02:00 1
2013-01-01 00:03:00 1
2013-01-01 00:04:00 1
2013-01-01 00:05:00 2
2013-01-01 00:06:00 2
2013-01-01 00:07:00 3
2013-01-01 00:08:00 3
2013-01-01 00:09:00 3
2013-01-01 00:10:00 1
2013-01-01 00:11:00 1

Note:

  • I don't know how to avoid that "list of minutes" table in MySQL. Doable in Oracle with a select ... from dual connect by level <= ... or similar.
  • You'll probably need to restrict the join on sessions more to avoid a full scan of that table (limiting the start date at least, start and end sounds better)
Related Question