Calculating 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

please help me how do i change the query to reduce the 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

 

Can you answer these questions?

What indexes does the table have?
How many rows are in the table?
Is this InnoDB or MyISAM or other?

Also, can you post the output of running EXPLAIN with this query?

 

I have post explain query, show create xydata and fm4features

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


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,
  `GsTime` 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`,`GsTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=70227 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

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

Code (markup):

 

I have post explain query, show create table xydata and fm4features

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


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,
  `GsTime` 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`,`GsTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=70227 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

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

Code (markup):

 

There's so much logic going on that it's a bit tough to determine what is specifically slowing everything down. I would start out by using parenthesis around the specific Indexed items in the where clause. I would also put an index on fm4features .DataId, and fm4features.Value.

Luckily you don't have many rows, so I think it is more a matter of finding the bottloenecks.

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  

Code (markup):

 

In xydata and fm4features there are 1,00000 rows,

I put index in fm4features.dadaid, fm4features.value

then i run your(above) query it's also take 65.978 sec

please reply there is any other options,

 

divide and conquer

perhaps you should try removing some functions to see where is the bottleneck.
eg remove order by, test timing.
remove sql query in the coalesce leaving only a field

If timing improve sufficiently, it probably indicate some issue with the part you removed.
P.S. I know my example of removing order by, etc is not helpful because u need the sorting eventually.
But at least you can know which parts are causing the slowness
66 sec for a query sounds extreme though

 

starttime takes so much time

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

 

If you are certain the above sql is causing the slowness, try
a) objectid, clientid should be indexed
b) gstime should be indexed with b-tree or some kind of tree structure. Because it does comparison, just make sure it isn't
some kind of hashing function since it is very inefficient with comparison

 

thanks for your reply

I used gstime index in b-tree but nothing to happen

it takes same time,

If any other options is there to reduce the execution time,

thanks in advance

 

perhaps try between instead of > < for date comparison.

if not, divide and conquer, perhaps by removing each where clause till u pinpoint
which is causing the slowness

 

b.gstime < a.gstime

it's only take 40 sec

here is the slowness

 

Good to read about that b.gstime < a.gstime is taking only 40 sec for updating hours...
Thanks for sharing this helpful information..!!

 

Please help me how to reduce execution time or any other way is there

 

Do you want to reduce execution time of your query?

 

yes the above takes so much time to execute the query,
please help me how to reduce the time execution ,

thanks in advance