Hi Everyone,

I have a solution to creating a report of our company's unit hours of
activity (time spent serving a call) based on an accompanying call log
table, however, my queries take HOURS and HOURS to run (8+). As a
result I can't provide the report as often as the powers that be would
like it. Our table contains more than 1 million records and sensitive
data so I can't provide test data. :/

Let me explain the situation.

- Start and Stop times for each call are available
USE stevensjn;

DROP TABLE IF EXISTS `tbluhadata`;
CREATE TABLE `tbluhadata` (
`CallId` bigint(20) default NULL,
`CallStart` datetime default NULL,
`CallEnd` datetime default NULL,
PRIMARY KEY (`CallId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `_helper`;
CREATE TABLE `_helper` (
`i` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `tbluhahours`;
CREATE TABLE tbluhahours (
StartDate datetime NOT NULL,
EndDate datetime NOT NULL,
PRIMARY KEY (`StartDate`,`EndDate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE _helper (i TINYINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO _helper (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7),
(8), (9);

SET @counter := -1;
SET @start := '2000-01-01 00:00:00';
SET @finish := '2010-12-31 23:59:59';
INSERT INTO tbluhahours (StartDate, EndDate)
SELECT
@start + INTERVAL H.c HOUR,
@start + INTERVAL H.c + 1 HOUR - INTERVAL 1 SECOND

FROM (
SELECT @counter := @counter + 1 AS c
FROM _helper h1 -- 10
CROSS JOIN _helper h2 -- 100
CROSS JOIN _helper h3 -- 1000
CROSS JOIN _helper h4 -- 10000
CROSS JOIN _helper h5 -- 100000
) H

WHERE
@start + INTERVAL H.c HOUR < @finish;
////////////////// end

Here is a set of queries I have put together that uses joins to
determine elapsed times for each hour. Perhaps optimizing these
queries will be enough to get the script working at a workable speed?
These are the 8 hour queries. Any suggestions at all would be
fantastic.

USE stevensjn;

CREATE TABLE tblUHADurations(
SELECT
tblUHAData.CallId,
tblUHAData.CallStart,
tblUHAData.CallEnd,
tblUHAHours.StartDate,
DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
UNIX_TIMESTAMP(tblUHAHours.EndDate)+1 -
UNIX_TIMESTAMP(tblUHAData.CallStart) AS Duration
FROM
tblUHAData INNER JOIN tblUHAHours ON
tblUHAHours.StartDate <= tblUHAData.CallStart AND
tblUHAData.CallStart <= tblUHAHours.EndDate AND
tblUHAHours.EndDate < tblUHAData.CallEnd
);

INSERT INTO tblUHADurations (
SELECT
tblUHAData.CallId,
tblUHAData.CallStart,
tblUHAData.CallEnd,
tblUHAHours.StartDate,
DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
3600 AS Duration
FROM
tblUHAData INNER JOIN tblUHAHours ON
tblUHAHours.StartDate > tblUHAData.CallStart AND
tblUHAHours.EndDate < tblUHAData.CallEnd
);

INSERT INTO tblUHADurations (
SELECT
tblUHAData.CallId,
tblUHAData.CallStart,
tblUHAData.CallEnd,
tblUHAHours.StartDate,
DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
UNIX_TIMESTAMP(tblUHAData.CallEnd)+1 -
UNIX_TIMESTAMP(tblUHAHours.StartDate) AS Duration
FROM
tblUHAData INNER JOIN tblUHAHours ON
tblUHAHours.StartDate <= tblUHAData.CallEnd AND
tblUHAData.CallEnd <= tblUHAHours.EndDate AND
tblUHAHours.StartDate > tblUHAData.CallStart
);


INSERT INTO tblUHADurations (
SELECT
tblUHAData.CallId,
tblUHAData.CallStart,
tblUHAData.CallEnd,
tblUHAHours.StartDate,
DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
UNIX_TIMESTAMP(tblUHAData.CallEnd)+1 -
UNIX_TIMESTAMP(tblUHAData.CallStart) AS Duration
FROM
tblUHAData INNER JOIN tblUHAHours ON
tblUHAHours.StartDate <= tblUHAData.CallStart AND
tblUHAData.CallEnd <= tblUHAHours.EndDate
);

Thanks for reading. With any luck someone has had to do something like
this in the past, of will find the challenge interesting.

- Jake