SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calculate Working hours

    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
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you provide the table schema?

  3. #3
    SitePoint Member
    Join Date
    Apr 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    explain output
    Code:
    1	PRIMARY	<derived2>	ALL					2749	100	Using temporary; Using filesort
    2	DERIVED	a	range	PRIMARY,id_objid	id_objid	16		33313	100	Using where; Using index
    2	DERIVED	f	ref	ID	ID	4	navl.a.ID	4	100	Using where
    3	DEPENDENT SUBQUERY	b	range	id_objid	id_objid	16		33313	100	Using where; Using index
    show create xydata
    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=70227 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
    show create fm4features
    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 post all the schema please reply
    Last edited by baktha.thalapthy; May 5, 2010 at 22:30. Reason: changing


Bookmarks

Posting Permissions

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