mysql select count on same table slow subquery

Question

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 :)

Answer 1

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.

Answer 2

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 ;