php - Slow MySQL query with multiple joins, max() and group by -

i've got serious problem. our intranet getting slower , slower. 1 of mainreasons seems slow mysql-query (it appears in slow-query.log). query asked every time intranet-site opened. looks this:

select w.datetime, w.user_id, w.status, e.lastname worktimes w inner join employees e on w.user_id=e.id right join (select max(datetime) datetime, user_id             worktimes             datetime>".$today." // variable of today 0.00 o'clock             , location='".$llocation['id']."' // variable of 1 of 9 locations             group user_id) v         on v.user_id=w.user_id , w.datetime=v.datetime order e.lastname; 

the worktimes-table greater 200k rows (momentary 90k testing reasons) , 13 columns. whole query goes through loop 3 9 cycles.

has idea how make queries faster?

edit: wished here explain-result.

id  select_type     table       type    possible_keys   key        key_len  ref               rows  1   primary         <derived2>      null            null       null     null              44006 using temporary; using filesort 1   primary         w               null            null       null     null              92378 using 1   primary         e           eq_ref  primary,id      primary    4        ais_v1.w.user_id      1 null 2   derived         worktimes   ref     location        location   767      const             44006 using index condition; using where; using temporary; using filesort 

the w table needs index on index(location, datetime). should improve performance.