Re: Query Optimisation of a slow query

[Rick James]

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.