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;

Thanks to Pavel Vorontsov.
the Answer is:
select a.id
,a.card_id
,week(a.activity_date,1)
,dayofweek(a.activity_date)
,a.activity_date
,sec_to_time(sum(time_to_sec(case when b.activity = 0 then b.activity_duration end))) AS rest_time
,sec_to_time(sum(time_to_sec(case when b.activity = 1 then b.activity_duration end))) AS standby_time
,sec_to_time(sum(time_to_sec(case when b.activity = 2 then b.activity_duration end))) AS work_time
,sec_to_time(sum(time_to_sec(case when b.activity = 3 then b.activity_duration end))) AS drive_time
,a.day_distance
from activities a inner join v_activity_info b on a.id = b.activity_id
group by a.id
,a.card_id
,week(a.activity_date,1)
,dayofweek(a.activity_date)
,a.activity_date
,a.day_distance;