MySQL Forums
Forum List  »  Optimizer & Parser

Query Optimisation of a slow query
Posted by: Gaurav Bhuyan
Date: September 13, 2011 03:26AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Query Optimisation of a slow query
3745
September 13, 2011 03:26AM
1389
September 16, 2011 10:21PM
1300
September 19, 2011 01:01AM
1175
September 20, 2011 09:20PM
1107
September 21, 2011 12:21AM
1162
September 21, 2011 02:38AM
1109
September 22, 2011 12:03AM
1101
September 20, 2011 01:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.