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
3323
September 13, 2011 03:26AM
Re: Query Optimisation of a slow query
1179
September 16, 2011 10:21PM
1090
September 19, 2011 01:01AM
1006
September 20, 2011 09:20PM
904
September 21, 2011 12:21AM
967
September 21, 2011 02:38AM
943
September 22, 2011 12:03AM
948
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.