MySQL data - Count number of phone calls at the same time

Question

I have a MySQL table with phone calls. Every row means one phone call. Columns are:

start_time
start_date
duration

I need to get a maximum phone calls called at the same time. It's because of telephone exchange dimensioning.

My solution is to create two timestamp columns timestamp_start and timestamp_end. Then I run a loop second by second, day by day and ask MySQL something like:

SELECT Count(*) FROM tbl WHERE start_date IN (thisday, secondday) AND "this_second_checking" BETWEEN timestamp_start AND timestamp_end;

It's quite slow. Is there a better solution? Thank you!

EDIT - I use this solution and it gives me proper results. There is used SQL layer dibi - http://dibiphp.com/cs/quick-start .

$starts = dibi::query("SELECT ts_start, ts_end FROM " . $tblname . " GROUP BY ts_start");
if(count($starts) > 0):
  foreach ($starts as $row) {
    if(isset($result)) unset($result);
    $result = dibi::query('SELECT Count(*) FROM ' . $tblname . ' WHERE "'.$row->ts_start.'" BETWEEN ts_start AND ts_end');
    $num = $result->fetchSingle();
    if($total_max < $num):
      $total_max = $num;
    endif;    
  }  
endif;
echo "Total MAX: " . $total_max;

Answer 1

Instead of running it second by second, you should for each row (phonecall) see what other phone calls were active at that time. After that you group all of the results by the row's ID, and check which has the maximum count. So basically something like this:

SELECT MAX(calls.count)
FROM (
    SELECT a.id, COUNT(*) AS count
    FROM tbl AS a
    INNER JOIN tbl AS b ON (
        (b.timestamp_start BETWEEN a.timestamp_start AND a.timestamp_end)
        OR
        (b.timestamp_end BETWEEN a.timestamp_start AND a.timestamp_end)
    )
    GROUP BY a.id
) AS calls

Creating an index on the timestamp columns will help as well.