Query Optimisation of a slow query

[Gaurav Bhuyan]

Hi I am new to mysql and a slow query is really bothering me.

The query is :

SELECT a.network_traffic_id,
SUM(IF((a.load_in - k.load_in) > 0,TIME_TO_SEC(TIMEDIFF(a.date_time,k.date_time)),NULL)) AS active_duration_in,
SUM(IF((a.load_out - k.load_out) > 0,TIME_TO_SEC(TIMEDIFF(a.date_time,k.date_time)),NULL)) AS active_duration_out,
SUM(IF((a.load_out - k.load_out)+(a.load_in - k.load_in)> 0,(TIME_TO_SEC(TIMEDIFF(a.date_time,k.date_time))),NULL))AS active_duration_total,

SUM((a.load_in - k.load_in)) AS load_in_total,
SUM((a.load_out-k.load_out)) AS load_out_total,
SUM(TIME_TO_SEC(TIMEDIFF(a.date_time,k.date_time))) AS duration_total,
MAX(IF(TIME_TO_SEC(TIMEDIFF(a.date_time,k.date_time)) < 120,(a.load_in - k.load_in) /TIME_TO_SEC(TIMEDIFF(a.date_time,k.date_time)) ,
NULL)) AS max_in_throughput,
MAX(IF(TIME_TO_SEC(TIMEDIFF(a.date_time,k.date_time)) < 120,(a.load_out - k.load_out) /TIME_TO_SEC(TIMEDIFF(a.date_time,k.date_time)) ,
NULL)) AS max_out_throughput,bearer

FROM p_network_traffic_throughput_tmp1 a,
p_network_traffic_throughput_tmp2 k,
p_network_traffic_bearer c

where a.network_traffic_id = k.network_traffic_id
and k.network_traffic_id=c.network_traffic_id
and a.id = k.id + 1

AND c.id = (SELECT MAX(id)
FROM p_network_traffic_bearer
WHERE network_traffic_id = a.network_traffic_id
AND date_time < a.date_time + interval 1 second)
GROUP BY network_traffic_id,bearer

The Explain output is :

1 PRIMARY c index PRIMARY,network_traffic_id,bearer_network_traffic_id bearer_network_traffic_id 5 (null) 2342 Using index; Using temporary; Using filesort


1 PRIMARY k ref traffic_id_date_time,network_traffic_id network_traffic_id 4 sp360_panel.c.network_traffic_id 21

1 PRIMARY a eq_ref PRIMARY,traffic_id_date_time,network_traffic_id PRIMARY 4 func 1 Using where

2 DEPENDENT SUBQUERY p_network_traffic_bearer ref network_traffic_id,bearer_network_traffic_id network_traffic_id 4 sp360_panel.a.network_traffic_id 1 Using where



The query takes around 5 minutes to execute. Any help is much appreciated.