Return to Question

added required other information

Source Link

edited Apr 13, 2015 at 6:46

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.

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.

Source Link

asked Apr 12, 2015 at 16:47

Nimeshka Srimal

mysql select count on same table slow subquery

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;

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

Thank you all in advance :)

mysql