Counting records with a point in time during the start and end time

Question

Suppose I have a table like this:

CREATE TABLE foo ( gid BIGINT PRIMARY KEY, starttime BIGINT, endtime BIGINT ); 

This table stores the start and end times of a series of games (in the format of "seconds from the era"). Now I want to know how many games were completed in the blink of an eye. This is a natural request:

 SET @t = UNIX_TIMESTAMP('2012-07-12 12:00:00'); SELECT COUNT(f.gid) FROM foo f WHERE @t BETWEEN f.starttime AND f.endtime; 

The complication is that I need to do this every five minutes (each game lasts only a couple of minutes, and we are there several thousand every hour) and, probably, lasting six months. I have a procedure to iterate over the date range that interests me and generate @t for five minute intervals. The problem is that the request is too slow. I currently store all @t in a separate table that I indexed, for example:

 CREATE TABLE bar ( interval BIGINT PRIMARY KEY ); 

So now I have a request:

 SELECT b.interval, COUNT(f.gid) FROM bar b LEFT JOIN foo f ON b.interval BETWEEN f.starttime AND f.endtime GROUP BY b.interval; 

This is too slow, and no amount of indexing in the "foo" table seems to help. This is, in my opinion, a standard problem, possibly using a standard request template, so appreciate any help here.

Answer 1

It seems to me that as soon as the interval passes, you will not need to again calculate the number of running games.

so why not add another column to bar ?

 CREATE TABLE bar ( interval BIGINT PRIMARY KEY, runningGames INT) 

thus, you should only set a scheduled task every five minutes.

 INSERT into bar SELECT UNIX_TIMESTAMP(NOW()), COUNT(*) FROM foo WHERE endtime is null 

and you don’t have to recreate the data for six months again and again

Answer 2

Alas, this is much easier with windows / analytic functions.

In mysql, you can use the following strategy to solve the problem.

Create a temporary start time table. The start temporary table has an auto-increment identifier that tracks the row number. Then insert the start points in order.

Create a temporary finite time table. At the end of the temporary table there is an auto-increment identifier that tracks the row number. Then insert the end times in order.

These tables have an accumulated number of starts and stops since the beginning of time.

Assuming that every 5-minute interval has at least one start and stop, we can query this table using:

 select t.time, s.seqnum - e.seqnum from (select <cast datetime to 5-minute interval> as time, seqnum from starts ) s full outer join (select <cast datetime to 5-minute interval> as time, seqnum from ends ) e on s.time = e.time 

This takes a cumulative number of starts and subtracts the cumulative number of stops for each interval. I assume you know better than I do how to crop / round time to the next 5 minutes in mysql.