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


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

Can you provide the table schema?

explain output


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

 
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


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