mysql another way of using GROUP BY

Question

I'm doing some time matching but it takes too much time.

Example i have

TABLE aaa

id | emp_id|shift_in               | shift_out             | actual_timein | actual_timout 
1  | 123   | "2014-10-11 01:00:00" | "2014-10-11 01:00:00" | null          | null

TABLE bbb status 0=out : 1=in

id| emp_id | status | timelog | 
1 | 123    | 1      |  "2014-10-11 01:03:00"
2 | 123    | 1      |  "2014-10-11 00:48:00"
3 | 123    | 1      |  "2014-10-10 21:14:00"
4 | 123    | 1      |  "2014-10-10 23:47:00"
1 | 123    | 1      |  "2014-10-11 08:01:00"
1 | 123    | 1      |  "2014-10-11 08:02:00"
1 | 123    | 1      |  "2014-10-11 08:03:00"

what im doing is updating the table bbb
actual_timein first then another statement for actual_timeout

UPDATE aaa c
  INNER JOIN(
      select *, MIN(time_log) as my_time from aaa w
         inner join bbb b on b.emp_id = a.emp_id and status = 1
and time_log < shift_in and TIME_TO_SEC(TIMEDIFF(a.shift_in, b.time_log))/3600  < 2
where b.timelog between '2014-10-01' and  '2014-10-31'
group by a.emp_id order by b.timelog asc
    ) d on d.emp_id = c.emp_id 
set c.actual_timein = d.my_time

assuming that the code works :) ....
any other way to do this?
if i have 200 employee it will have 3000 in table aaa per 15days with schedule 200*15
thanks...

Answer 1

This is your query:

UPDATE aaa c INNER JOIN
       (select *, MIN(time_log) as my_time
        from aaa w inner join
             bbb b
             on b.emp_id = a.emp_id and b.status = 1 and time_log < shift_in and 
                TIME_TO_SEC(TIMEDIFF(a.shift_in, b.time_log))/3600  < 2
        where b.timelog between '2014-10-01' and '2014-10-31'
        group by a.emp_id
        order by b.timelog asc
      ) d
      on d.emp_id = c.emp_id 
    set c.actual_timein = d.my_time;

Initial observations: the * is unnecessary (and unwise in an aggregation query). The order by is unnecessary. So, try this version:

UPDATE aaa c INNER JOIN
       (select a.emp_id MIN(time_log) as my_time
        from aaa w inner join
             bbb b
             on b.emp_id = a.emp_id and b.status = 1 and b.time_log < shift_in and 
                TIME_TO_SEC(TIMEDIFF(a.shift_in, b.time_log))/3600  < 2
        where b.timelog between '2014-10-01' and '2014-10-31'
        group by a.emp_id
      ) d
      on d.emp_id = c.emp_id 
    set c.actual_timein = d.my_time;

Second, indexes can probably help. I would suggest bbb(status, time_log, emp_id) for the inner query.