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
3364
September 13, 2011 03:26AM
1198
September 16, 2011 10:21PM
1120
September 19, 2011 01:01AM
1021
September 20, 2011 09:20PM
928
September 21, 2011 12:21AM
986
September 21, 2011 02:38AM
957
September 22, 2011 12:03AM
964
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.