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

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

Code (markup):

 

Make sure that the fields you search for have an index that will speed up the query.

 

i have used index also

In xydata->gstime, objectid, clientid are index
In fm4features->Id, dataid, value are index

but it will take the more execution time compare with previous query