Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can anyone help me tuning this query.....

explain extended 

 SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a  
LEFT OUTER JOIN 
 (SELECT msisdn 
  FROM as_treat_pre_usage_30days 
  GROUP BY msisdn 
  HAVING SUM(std_total_og_mou)>0) b ON a.msisdn=b.msisdn
WHERE 
 b.msisdn IS NULL 
 AND a.call_dt BETWEEN '2013-03-27' AND '2013-04-28';

    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    | id | select_type | table                     | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra                             |
    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    |  1 | PRIMARY     | a                         | ALL   | ix_cd         | NULL    | NULL    | NULL | 73985537 |    50.00 | Using where; Using temporary      |
    |  1 | PRIMARY     | <derived2>                | ALL   | NULL          | NULL    | NULL    | NULL |  1121664 |   100.00 | Using where; Not exists; Distinct |
    |  2 | DERIVED     | as_treat_pre_usage_30days | index | NULL          | PRIMARY | 14      | NULL |  3033621 |   100.00 |                                   |
    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    3 rows in set, 1 warning (1.70 sec)

________________________________________________________________________

explain extended 

    SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a FORCE INDEX(ix_cd) LEFT OUTER JOIN
    (SELECT msisdn FROM as_treat_pre_usage_30days GROUP BY msisdn HAVING SUM(std_total_og_mou)>0 )b
    ON a.msisdn=b.msisdn
    WHERE b.msisdn IS NULL AND a.call_dt BETWEEN '2013-03-27' AND '2013-04-28'; 

    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    | id | select_type | table                     | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra                             |
    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+
    |  1 | PRIMARY     | a                         | range | ix_cd         | ix_cd   | 4       | NULL | 36992756 |   100.00 | Using where; Using temporary      |
    |  1 | PRIMARY     | <derived2>                | ALL   | NULL          | NULL    | NULL    | NULL |  1121664 |   100.00 | Using where; Not exists; Distinct |
    |  2 | DERIVED     | as_treat_pre_usage_30days | index | NULL          | PRIMARY | 14      | NULL |  3033621 |   100.00 |                                   |
    +----+-------------+---------------------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------+

    ____________________________________________________

TABLE SCHEMA

____________________________________________________
CREATE TABLE `as_treat_pre_usage_30days` (
  `msisdn` varchar(12) NOT NULL COMMENT 'Subscriber number',
  `local_a2a_og_mou` bigint(20) DEFAULT NULL COMMENT 'Local A2A OG MOU of the subs',
  `local_a2o_og_mou` bigint(20) DEFAULT NULL COMMENT 'Local A2O OG MOU of the subs',
  `std_total_og_mou` bigint(20) DEFAULT NULL COMMENT 'STD OG MOU of the subs',
  `total_og_mou` bigint(20) DEFAULT NULL COMMENT 'Total OG MOU of the subs',
  PRIMARY KEY (`msisdn`),
  KEY `ix_std` (`std_total_og_mou`)
) ENGINE=InnoDB
____________________________________________________
CREATE TABLE `std_msc_opr_wise` (
  `msisdn` varchar(12) DEFAULT NULL COMMENT 'Mobile Number of the Subscriber',
  `call_dt` date DEFAULT NULL,
  `destination_type` varchar(5) DEFAULT NULL COMMENT 'Destination Type - A2A or A2O',
  `duration` int(6) DEFAULT NULL COMMENT 'Call Duration in Seconds',
  KEY `ix_ms` (`msisdn`),
  KEY `ix_cd` (`call_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(call_dt))
(PARTITION p20130120 VALUES LESS THAN (735253) ENGINE = InnoDB,
 PARTITION p20130121 VALUES LESS THAN (735254) ENGINE = InnoDB,
 PARTITION p20130122 VALUES LESS THAN (735255) ENGINE = InnoDB,
 PARTITION p20130123 VALUES LESS THAN (735256) ENGINE = InnoDB,
 PARTITION p20130124 VALUES LESS THAN (735257) ENGINE = InnoDB,
 PARTITION p20130126 VALUES LESS THAN (735259) ENGINE = InnoDB,
 PARTITION p20130127 VALUES LESS THAN (735260) ENGINE = InnoDB,
 PARTITION p20130128 VALUES LESS THAN (735261) ENGINE = InnoDB,
 PARTITION p20130129 VALUES LESS THAN (735262) ENGINE = InnoDB,
 PARTITION p20130130 VALUES LESS THAN (735263) ENGINE = InnoDB,
 PARTITION p20130131 VALUES LESS THAN (735264) ENGINE = InnoDB,
 PARTITION p20130201 VALUES LESS THAN (735265) ENGINE = InnoDB,
 PARTITION p20130202 VALUES LESS THAN (735266) ENGINE = InnoDB,
 PARTITION p20130203 VALUES LESS THAN (735267) ENGINE = InnoDB,
 PARTITION p20130204 VALUES LESS THAN (735268) ENGINE = InnoDB,
 PARTITION p20130205 VALUES LESS THAN (735269) ENGINE = InnoDB,
 PARTITION p20130206 VALUES LESS THAN (735270) ENGINE = InnoDB,
 PARTITION p20130207 VALUES LESS THAN (735271) ENGINE = InnoDB,
 PARTITION p20130208 VALUES LESS THAN (735272) ENGINE = InnoDB,
 PARTITION p20130209 VALUES LESS THAN (735273) ENGINE = InnoDB,
 PARTITION p20130210 VALUES LESS THAN (735274) ENGINE = InnoDB,
 PARTITION p20130211 VALUES LESS THAN (735275) ENGINE = InnoDB,
 PARTITION p20130212 VALUES LESS THAN (735276) ENGINE = InnoDB,
 PARTITION p20130213 VALUES LESS THAN (735277) ENGINE = InnoDB,
 PARTITION p20130214 VALUES LESS THAN (735278) ENGINE = InnoDB,
 PARTITION p20130215 VALUES LESS THAN (735279) ENGINE = InnoDB,
 PARTITION p20130216 VALUES LESS THAN (735280) ENGINE = InnoDB,
 PARTITION p20130217 VALUES LESS THAN (735281) ENGINE = InnoDB,
 PARTITION p20130218 VALUES LESS THAN (735282) ENGINE = InnoDB,
 PARTITION p20130219 VALUES LESS THAN (735283) ENGINE = InnoDB,
 PARTITION p20130220 VALUES LESS THAN (735284) ENGINE = InnoDB,
 PARTITION p20130221 VALUES LESS THAN (735285) ENGINE = InnoDB,
 PARTITION p20130222 VALUES LESS THAN (735286) ENGINE = InnoDB,
 PARTITION p20130223 VALUES LESS THAN (735287) ENGINE = InnoDB,
 PARTITION p20130224 VALUES LESS THAN (735288) ENGINE = InnoDB,
 PARTITION p20130225 VALUES LESS THAN (735289) ENGINE = InnoDB,
 PARTITION p20130226 VALUES LESS THAN (735290) ENGINE = InnoDB,
 PARTITION p20130227 VALUES LESS THAN (735291) ENGINE = InnoDB,
 PARTITION p20130228 VALUES LESS THAN (735292) ENGINE = InnoDB,
 PARTITION p20130301 VALUES LESS THAN (735293) ENGINE = InnoDB,
 PARTITION p20130302 VALUES LESS THAN (735294) ENGINE = InnoDB,
 PARTITION p20130303 VALUES LESS THAN (735295) ENGINE = InnoDB,
 PARTITION p20130304 VALUES LESS THAN (735296) ENGINE = InnoDB,
 PARTITION p20130305 VALUES LESS THAN (735297) ENGINE = InnoDB,
 PARTITION p20130306 VALUES LESS THAN (735298) ENGINE = InnoDB,
 PARTITION p20130307 VALUES LESS THAN (735299) ENGINE = InnoDB,
 PARTITION p20130308 VALUES LESS THAN (735300) ENGINE = InnoDB,
 PARTITION p20130309 VALUES LESS THAN (735301) ENGINE = InnoDB,
 PARTITION p20130310 VALUES LESS THAN (735302) ENGINE = InnoDB,
 PARTITION p20130311 VALUES LESS THAN (735303) ENGINE = InnoDB,
 PARTITION p20130312 VALUES LESS THAN (735304) ENGINE = InnoDB,
 PARTITION p20130313 VALUES LESS THAN (735305) ENGINE = InnoDB,
 PARTITION p20130314 VALUES LESS THAN (735306) ENGINE = InnoDB,
 PARTITION p20130315 VALUES LESS THAN (735307) ENGINE = InnoDB,
 PARTITION p20130316 VALUES LESS THAN (735308) ENGINE = InnoDB,
 PARTITION p20130317 VALUES LESS THAN (735309) ENGINE = InnoDB,
 PARTITION p20130318 VALUES LESS THAN (735310) ENGINE = InnoDB,
 PARTITION p20130319 VALUES LESS THAN (735311) ENGINE = InnoDB,
 PARTITION p20130320 VALUES LESS THAN (735312) ENGINE = InnoDB,
 PARTITION p20130321 VALUES LESS THAN (735313) ENGINE = InnoDB,
 PARTITION p20130322 VALUES LESS THAN (735314) ENGINE = InnoDB,
 PARTITION p20130323 VALUES LESS THAN (735315) ENGINE = InnoDB,
 PARTITION p20130324 VALUES LESS THAN (735316) ENGINE = InnoDB,
 PARTITION p20130325 VALUES LESS THAN (735317) ENGINE = InnoDB,
 PARTITION p20130326 VALUES LESS THAN (735318) ENGINE = InnoDB,
 PARTITION p20130327 VALUES LESS THAN (735319) ENGINE = InnoDB,
 PARTITION p20130328 VALUES LESS THAN (735320) ENGINE = InnoDB,
 PARTITION p20130329 VALUES LESS THAN (735321) ENGINE = InnoDB,
 PARTITION p20130330 VALUES LESS THAN (735322) ENGINE = InnoDB,
 PARTITION p20130331 VALUES LESS THAN (735323) ENGINE = InnoDB,
 PARTITION p20130401 VALUES LESS THAN (735324) ENGINE = InnoDB,
 PARTITION p20130402 VALUES LESS THAN (735325) ENGINE = InnoDB,
 PARTITION p20130403 VALUES LESS THAN (735326) ENGINE = InnoDB,
 PARTITION p20130404 VALUES LESS THAN (735327) ENGINE = InnoDB,
 PARTITION p20130405 VALUES LESS THAN (735328) ENGINE = InnoDB,
 PARTITION p20130406 VALUES LESS THAN (735329) ENGINE = InnoDB,
 PARTITION p20130407 VALUES LESS THAN (735330) ENGINE = InnoDB,
 PARTITION p20130408 VALUES LESS THAN (735331) ENGINE = InnoDB,
 PARTITION p20130409 VALUES LESS THAN (735332) ENGINE = InnoDB,
 PARTITION p20130410 VALUES LESS THAN (735333) ENGINE = InnoDB,
 PARTITION p20130411 VALUES LESS THAN (735334) ENGINE = InnoDB,
 PARTITION p20130413 VALUES LESS THAN (735336) ENGINE = InnoDB,
 PARTITION p20130414 VALUES LESS THAN (735337) ENGINE = InnoDB,
 PARTITION p20130415 VALUES LESS THAN (735338) ENGINE = InnoDB,
 PARTITION p20130416 VALUES LESS THAN (735339) ENGINE = InnoDB,
 PARTITION p20130417 VALUES LESS THAN (735340) ENGINE = InnoDB,
 PARTITION p20130418 VALUES LESS THAN (735341) ENGINE = InnoDB,
 PARTITION p20130419 VALUES LESS THAN (735342) ENGINE = InnoDB,
 PARTITION p20130420 VALUES LESS THAN (735343) ENGINE = InnoDB,
 PARTITION p20130421 VALUES LESS THAN (735344) ENGINE = InnoDB,
 PARTITION p20130422 VALUES LESS THAN (735345) ENGINE = InnoDB,
 PARTITION p20130423 VALUES LESS THAN (735346) ENGINE = InnoDB,
 PARTITION p20130424 VALUES LESS THAN (735347) ENGINE = InnoDB,
 PARTITION p20130425 VALUES LESS THAN (735348) ENGINE = InnoDB,
 PARTITION p20130426 VALUES LESS THAN (735349) ENGINE = InnoDB,
 PARTITION p20130427 VALUES LESS THAN (735350) ENGINE = InnoDB,
 PARTITION p20130428 VALUES LESS THAN (735351) ENGINE = InnoDB,
 PARTITION p20130429 VALUES LESS THAN (735352) ENGINE = InnoDB,
 PARTITION p20130430 VALUES LESS THAN (735353) ENGINE = InnoDB,
 PARTITION p20130501 VALUES LESS THAN (735354) ENGINE = InnoDB) *
share|improve this question

1 Answer 1

up vote 1 down vote accepted

You can certainly try this;

SELECT DISTINCT msisdn 
FROM std_msc_opr_wise  
WHERE 
 call_dt BETWEEN '2013-03-27' AND '2013-04-28'
 AND msisdn NOT IN (
   select msisdn
   from as_treat_pre_usage_30days
   group by msisdn
   having SUM(std_total_og_mou)>0
   )
share|improve this answer
    
Thanks a lot druzin.... –  Vinay Apr 29 '13 at 17:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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