stevensjn@gmail.com wrote:
> 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

I seem to remember posting about this question the other day and saying that
group by was a better solution (I just checked and I certainly did, but you
never responded to my poost).