1

I've a small table which contains a call log history. Now I want to make a report from this, so I have to find the number of outgoing and incoming calls.

Company has a few extenstions ranging from 100 - 600. So In order to find out if it's an outgoing call, I check if the "dest" is not between 100 - 600 and I get the count using a sub query. (Meaning, two sub queries to get Outgoing and Incoming)

But the problem is, these two sub queries make the query a lot slow, It takes like 3 seconds. (without using these two sub queries it takes only 0.3s).

I read so many questions and answers and found out that the best approach would be to use a join instead of subqueries because of Mysql's low performance on subqueries.

But when I try to join the same table and try to get the same result, it always gives 0 rows. I can't understand how to get the same output using a join.

Here's my Query (with SQ)

SELECT SQL_CALC_FOUND_ROWS DATE(calldate) as clldate,
    clid as caller_id,
    src as source_ext,
    dst as dest_ext,
    (select count(clid) from cdr where src between 100 and 600 and clid = caller_id and DATE(calldate) = clldate) as outbound,
    (select count(clid) from cdr where dst not between 100 and 600 and clid = caller_id and DATE(calldate) = clldate) as inbound,
    sum(duration)/60 as call_duration
FROM cdr
group by DATE(calldate), clid
order by calldate LIMIT 0 , 10;

Here is the explain result:

id  select_typetable    type    possible_keys   key key_len ref rows    Extra
1   PRIMARY cdr ALL NULL    NULL    NULL    NULL    1765    "Using temporary; Using filesort"
3   "DEPENDENT SUBQUERY"    cdr ALL dst NULL    NULL    NULL    1765    "Using where"
2   "DEPENDENT SUBQUERY"    cdr ALL NULL    NULL    NULL    NULL    1765    "Using where"

Here is the show create table:

'CREATE TABLE `cdr` (
  `calldate` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `clid` varchar(80) NOT NULL DEFAULT '''',
  `src` varchar(80) NOT NULL DEFAULT '''',
  `dst` varchar(80) NOT NULL DEFAULT '''',
  `dcontext` varchar(80) NOT NULL DEFAULT '''',
  `channel` varchar(80) NOT NULL DEFAULT '''',
  `dstchannel` varchar(80) NOT NULL DEFAULT '''',
  `lastapp` varchar(80) NOT NULL DEFAULT '''',
  `lastdata` varchar(80) NOT NULL DEFAULT '''',
  `duration` int(11) NOT NULL DEFAULT ''0'',
  `billsec` int(11) NOT NULL DEFAULT ''0'',
  `disposition` varchar(45) NOT NULL DEFAULT '''',
  `amaflags` int(11) NOT NULL DEFAULT ''0'',
  `accountcode` varchar(20) NOT NULL DEFAULT '''',
  `uniqueid` varchar(32) NOT NULL DEFAULT '''',
  `userfield` varchar(255) NOT NULL DEFAULT '''',
  `did` varchar(50) NOT NULL DEFAULT '''',
  `recordingfile` varchar(255) NOT NULL DEFAULT '''',
  `cnum` varchar(40) NOT NULL DEFAULT '''',
  `cnam` varchar(40) NOT NULL DEFAULT '''',
  `outbound_cnum` varchar(40) NOT NULL DEFAULT '''',
  `outbound_cnam` varchar(40) NOT NULL DEFAULT '''',
  `dst_cnam` varchar(40) NOT NULL DEFAULT '''',
  KEY `calldate` (`calldate`),
  KEY `dst` (`dst`),
  KEY `accountcode` (`accountcode`),
  KEY `ClldateCid` (`calldate`,`clid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

Can anybody guide me on how to get this properly done without using sub queries.

Thank you all in advance :)

  • 1
    Please provide SHOW CREATE TABLE and EXPLAIN SELECT ... – Rick James Apr 12 '15 at 18:28
  • Added both. Please see the updated question. Thank you. – Nimeshka Srimal Apr 13 '15 at 6:47
2

What is the datatype of clldate and calldate? If they are both DATE, get rid of the function calls (DATE()) around them; it prevents use of indexes.

If wither or both is DATETIME (or TIMESTAMP), then we need to rephrase the query to make it more efficient.

Add compound index:
INDEX(caller_id, calldate)

If calldate is DATE, then also add
INDEX(calldate, caller_id)

How big is cdr? How big will it eventually become?

Does the performance improve much is you get rid of SQL_CALC_FOUND_ROWS? Consider whether you really need it.

Will you be purging 'old' data?

After you have answered my questions, I will consider whether PARTITIONing is worth doing.

  • Data type of calldate is datetime. I added the index as you advised, but still it takes the same time. I guess it's because I use DATE() around the field as you said.removing the DATE() makes the query run very fast like in less than 1 sec, but the problem is then it gives me a different result because I get the count from the call date. (calldate field contains both date and time, so it doesn't count record by date only.) Do you have any solution to this? Thank u very much – Nimeshka Srimal Apr 13 '15 at 6:50
  • Omitting SQL_CALC_FOUND_ROWS doesn't make much difference (only reduces ~0.01s). cdr is still a small table with 1400 records, but eventually it would get bigger as they get more and more calls with time. – Nimeshka Srimal Apr 13 '15 at 6:55
0

Rick James makes some good points in his answer. Additionally, I don't think there is any need for subqueries and this is equivalent:

SELECT SQL_CALC_FOUND_ROWS 
    DATE(calldate) AS clldate,
    clid AS caller_id,
    src AS source_ext,
    dst AS dest_ext,
    count(CASE WHEN src BETWEEN 100 AND 600 THEN clid END) AS outbound,
    count(CASE WHEN dst NOT BETWEEN 100 AND 600 THEN clid END) AS inbound,
    sum(duration)/60 AS call_duration
FROM cdr
GROUP BY DATE(calldate), clid
ORDER BY clldate LIMIT 0 , 10 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.