CODEWITHSUNDEEP
mysqlquery-optimization
nKandel
nKandel

Reputation: 2575

Optimize query get total call duration of day per subscriber

I have view HW02 created from table call_details having following structure.

pri_key | calling_no | called_no | answer_date_time | Duration

and I have to find total duration called by each subscriber on a day. and create view as 

create view hw02 as 
select calling_no, day(answer_date_time) as days,duration from call_details;

and I calculate total_duration of each subscriber per days as 

select a.calling_no,a.days,sum(b.duration) 
from hw02 as a, hw02 as b 
where a.calling_no=b.calling_no and a.days=b.days;

This query takes lots of time to execute. So my question is how to optimize this query. (Data :- around 150,000 rows)

Upvotes: 1

Views: 539

Answers (1)

Akash
Akash

Reputation: 5012

Try this, should be faster and serve your purpose

SELECT
  calling_no, 
  DATE(answer_date_time) as day,
  SUM( duration ) 
FROM 
  call_details
GROUP BY
  calling_no,
  DATE(answer_date_time)

A self-join on the view itself is not needed in your case, all we want is the total duration a user calls to other users, grouped by the date.

The call duration, I suppose for a particular call would be same for the called and the calling user (records). The day() function used by you, would not return the right results, if you have data for multiple months, hence I have used the date function instead

more on datetime functions in mysql, https://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Upvotes: 1

Related Questions