MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query Optimisation of a slow query
Posted by: Rick James
Date: September 16, 2011 10:21PM

For reference:
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

Please provide the rest of this info:
To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

It sounds like bearer_network_traffic_id is NULLable. Some reason for it not being NOT NULL?

The subquery with the MAX() can probably be turned into a "group by trick" type of subquery.

Options: ReplyQuote


Subject
Views
Written By
Posted
3482
September 13, 2011 03:26AM
Re: Query Optimisation of a slow query
1244
September 16, 2011 10:21PM
1185
September 19, 2011 01:01AM
1058
September 20, 2011 09:20PM
978
September 21, 2011 12:21AM
1024
September 21, 2011 02:38AM
996
September 22, 2011 12:03AM
989
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.