Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: Calculate Working hours

    Hello,

    I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. Please help to reduce the execution time of this query or if there is any other way to calculate working hours

    The following query take 63.499 sec

    Code:
    SELECT sql_calc_found_rows                                          gstime, 
           MAX(stoptime)                                                AS mx, 
           MIN(starttime)                                               AS mn, 
           Sec_to_time(SUM(Time_to_sec(Timediff(stoptime, starttime)))) AS totalworktime 
    FROM   (SELECT gstime, 
                   gstime                                               AS stoptime, 
                   Coalesce((SELECT MAX(b.gstime) 
                             FROM   xydata b 
                             WHERE  objectid = '17' 
                                    AND clientid = '1' 
                                    AND gstime > '2010-04-20 08:22:27' 
                                    AND gstime < '2010-04-26 10:22:27' 
                                    AND b.objectid = a.objectid 
                                    AND b.gstime < a.gstime), gstime) AS starttime 
            FROM   xydata a 
                   INNER JOIN fm4features f 
                     ON f.id = a.id 
            WHERE  objectid = '17' 
                   AND clientid = '1' 
                   AND gstime > '2010-04-20 08:22:27' 
                   AND gstime < '2010-04-26 10:22:27' 
                   AND f.dataid = '1' 
                   AND f.VALUE = '1') derived 
    GROUP  BY Date_format(gstime, '%Y-%m-%d') 
    ORDER  BY gstime ASC
    please help me how do i change the query to reduce the sec.

    Bakthavachalam E

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Without your table definitions and approximate numbers of records for each of the tables it is difficult to determine whether any improvements can be made. Can you also provide a list of the indexes on each of the tables.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2010
    Posts
    4

    calculate working hours

    explain output

    Code:
    1	PRIMARY	<derived2>	ALL					2749	100	Using temporary; Using filesort
    2	DERIVED	a	range	PRIMARY,id_objid	id_objid	16		33392	100	Using where; Using index
    2	DERIVED	f	ref	ID	ID	4	navl.a.ID	3	100	Using where
    3	DEPENDENT SUBQUERY	b	range	id_objid	id_objid	16		33392	100	Using where; Using index
    and

    Xydata Show create table

    Code:
    xydata	CREATE TABLE `xydata` (
      `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `Longi` varchar(255) DEFAULT NULL,
      `Lat` varchar(255) DEFAULT NULL,
      `Altitude` int(11) NOT NULL,
      `Angle` int(11) NOT NULL,
      `Satellite` int(11) NOT NULL,
      `Speed` int(11) NOT NULL,
      `ObjectId` int(10) unsigned NOT NULL,
      `ClientId` int(10) unsigned NOT NULL,
      `GpsTime` datetime NOT NULL,
      `Location` varchar(255) DEFAULT NULL,
      `City` varchar(255) DEFAULT NULL,
      `State` varchar(255) DEFAULT NULL,
      `Distance` varchar(100) DEFAULT NULL,
      `Times` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`ID`),
      KEY `id_objid` (`ObjectId`,`ClientId`,`GpsTime`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=70354 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
    and Fm4features show create table

    Code:
    fm4features	CREATE TABLE `fm4features` (
      `ID` int(10) unsigned NOT NULL DEFAULT '0',
      `DataId` int(11) NOT NULL,
      `Value` varchar(20) NOT NULL,
      KEY `ID` (`ID`),
      CONSTRAINT `new_xydata_id` FOREIGN KEY (`ID`) REFERENCES `xydata` (`ID`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
    i have post all the output please give the reply
    thanks in advance
    Last edited by baktha.thalapathy; 05-01-10 at 05:52. Reason: i put the exact output

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •