Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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)
share|improve this answer
    
This has been a great help. Thanks. –  Duncan Fairley Mar 3 '13 at 11:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.