title: Counting records with a time instant within a start and end time
body: <p>Suppose I have a table like this:</p>
  
  <pre><code>CREATE TABLE foo (
      gid BIGINT PRIMARY KEY,
      starttime BIGINT,
      endtime BIGINT
  );
  </code></pre>
  
  <p>This table stores the start and end times of a series of games (in "seconds from epoch" format). Now, I want to know how many games were running at a give instant. This is the natural query:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The complication is that I need to do this every five minutes (each game lasts only a couple of minutes, and we there are several thousand every hour) and likely for a six-month duration. I do have the procedure to loop over the date range I am interested in and generate the @t for five-minute intervals. The problem is that the query is far too slow. I am currently storing all @t in a separate table which I have indexed, like this:</p>
  
  <pre><code>CREATE TABLE bar (
      interval BIGINT PRIMARY KEY
  );
  </code></pre>
  
  <p>So the query I now have is:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>This is too slow, and no amount on indexing on the table "foo" seems to help. This I feel is a standard problem with perhaps a standard query pattern, so would appreciate any help here.</p>