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) *
Best Answer
You can certainly try this;