Hi,
I have some select statement that is so slow that it's required to be optimized. Unforetunately I have at the moment no idea how. Select has 4 subselects and this is the reason why it works so long. But I donn't know how to avoid this subselects. I've tried with group by, but it was even slower and gave wrong results.
Here is the query:
select a.id,a.card_id,week(a.activity_date,1), dayofweek(a.activity_date),a.activity_date,
(select sec_to_time(sum(time_to_sec(b.activity_duration))) from v_activity_info b where ((a.id = b.activity_id) and (b.activity = 0))) AS rest_time,
(select sec_to_time(sum(time_to_sec(c.activity_duration))) from v_activity_info c where ((a.id = c.activity_id) and (c.activity = 1))) AS standby_time,
(select sec_to_time(sum(time_to_sec(d.activity_duration))) from v_activity_info d where ((a.id = d.activity_id) and (d.activity = 2))) AS work_time,
(select sec_to_time(sum(time_to_sec(e.activity_duration))) from v_activity_info e where ((a.id = e.activity_id) and (e.activity = 3))) AS drive_time,
a.day_distance from activities a;