CODEWITHSUNDEEP
mysqlsqlperformancesubqueryupdates
Aamir
Aamir

Reputation: 758

how to optimize update statement using subquery?

I am running a update statement:

UPDATE ACTION a 
INNER JOIN subscriberinfo s ON a.subscriberId=s.id 
SET a.exceedusage = (SELECT FORMAT(((SUM(dataVolumeDownLink + dataVolumeUpLink))/1048576),2) 
            FROM cdr c 
            WHERE c.msisdn=s.msisdn 
            AND c.eventDate>=a.createdon 
            AND c.eventDate <= a.actionTakenOn) 
WHERE a.remark='Reason : Data limit crossed' 
AND a.exceedusage IS NULL;

But its taking too much time because of CDR table(millions of row). Is there any way I can rewrite this query so it can work fast?

EDIT:

Table structure of Action:

CREATE TABLE `action` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `actionTakenOn` datetime DEFAULT NULL,
  `actionType` varchar(255) DEFAULT NULL,
  `cdrCreatedOn` datetime DEFAULT NULL,
  `cdrEventDate` datetime DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `errorDescription` longtext,
  `params` longtext,
  `remark` longtext,
  `requestedOn` datetime DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `subscriberDetails` longtext,
  `takenBy` varchar(255) DEFAULT NULL,
  `subscriberId` bigint(20) DEFAULT NULL,
  `ticketId` bigint(20) DEFAULT NULL,
  `dataPlanEndTime` datetime DEFAULT NULL,
  `dataPlanStartTime` datetime DEFAULT NULL,
  `dataUsage` bigint(20) DEFAULT NULL,
  `dataplanName` varchar(255) DEFAULT NULL,
  `exceedUsage` bigint(20) DEFAULT NULL,
  `isNotified` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKAB2F7E36E90F678D` (`subscriberId`),
  KEY `FKAB2F7E3664633B07` (`ticketId`)
) ENGINE=MyISAM AUTO_INCREMENT=81534 DEFAULT CHARSET=latin1;

for Subscriberinfo:

CREATE TABLE `subscriberinfo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `imsi` varchar(255) DEFAULT NULL,
  `simType` varchar(45) DEFAULT NULL,
  `dataPlanStartTime` datetime DEFAULT NULL,
  `dataPlanEndTime` datetime DEFAULT NULL,
  `dataplan` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `validDays` varchar(255) DEFAULT NULL,
  `deviceName` varchar(500) DEFAULT NULL,
  `lastDataPlanUpdatedOn` datetime DEFAULT NULL,
  `lastDeviceUpdatedOn` datetime DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `dataplanType` varchar(255) DEFAULT NULL,
  `msisdn` bigint(20) DEFAULT NULL,
  `dataLeft` bigint(20) DEFAULT NULL,
  `billingSysDataPlanEndTime` datetime DEFAULT NULL,
  `billingSysDataPlanStartTime` datetime DEFAULT NULL,
  `billingSysValidDays` int(11) DEFAULT NULL,
  `dataUsage` bigint(20) DEFAULT NULL,
  `planDetail` varchar(255) DEFAULT NULL,
  `currentSpeedLimit` varchar(255) DEFAULT NULL,
  `lastBillingSysUpdatedOn` datetime DEFAULT NULL,
  `lastUpdatedOn` datetime DEFAULT NULL,
  `deviceType` varchar(255) DEFAULT NULL,
  `lastImsiUpdatedOn` datetime DEFAULT NULL,
  `skipCheck` tinyint(1) NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `msisdn_UNIQUE` (`msisdn`)
) ENGINE=InnoDB AUTO_INCREMENT=49032 DEFAULT CHARSET=latin1;

for CDR:

CREATE TABLE `cdr` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dataPacketDownLink` bigint(20) DEFAULT NULL,
  `dataPacketUpLink` bigint(20) DEFAULT NULL,
  `dataPlanEndTime` datetime DEFAULT NULL,
  `dataPlanStartTime` datetime DEFAULT NULL,
  `dataVolumeDownLink` bigint(20) DEFAULT NULL,
  `dataVolumeUpLink` bigint(20) DEFAULT NULL,
  `dataplan` varchar(255) DEFAULT NULL,
  `dataplanType` varchar(255) DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `deviceName` varchar(500) DEFAULT NULL,
  `duration` int(11) NOT NULL,
  `effectiveDuration` int(11) NOT NULL,
  `hour` int(11) DEFAULT NULL,
  `eventDate` datetime DEFAULT NULL,
  `msisdn` bigint(20) DEFAULT NULL,
  `quarter` int(11) DEFAULT NULL,
  `validDays` int(11) DEFAULT NULL,
  `dataLeft` bigint(20) DEFAULT NULL,
  `completedOn` datetime DEFAULT NULL,
  `causeForRecClosing` bigint(20) DEFAULT NULL,
  `roaming` tinyint(1) DEFAULT NULL,
  `servedBSAddress` varchar(255) DEFAULT NULL,
  `simType` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_msisdn` (`msisdn`)
) ENGINE=MyISAM AUTO_INCREMENT=2580713 DEFAULT CHARSET=latin1;

Upvotes: 0

Views: 65

Answers (2)

Kickstart
Kickstart

Reputation: 21533

Possibly change the whole thing to a series of JOIN.

This is very much a guess (I am not sure on how your tables hang together, hence not sure on the GROUP BY), but maybe something like this:-

UPDATE ACTION a 
INNER JOIN subscriberinfo s ON a.subscriberId=s.id 
INNER JOIN cdr c ON c.msisdn=s.msisdn AND c.eventDate BETWEEN a.createdon  AND a.actionTakenOn
SET a.exceedusage = FORMAT(((SUM(c.dataVolumeDownLink + c.dataVolumeUpLink))/1048576),2) 
WHERE a.remark='Reason : Data limit crossed' 
AND a.exceedusage IS NULL
GROUP BY a.subscriberId;

Upvotes: 0

StanislavL
StanislavL

Reputation: 57421

UPDATE ACTION a 
  INNER JOIN subscriberinfo s ON a.subscriberId=s.id 
  INNER JOIN (SELECT FORMAT(((SUM(dataVolumeDownLink + dataVolumeUpLink))/1048576),2) as val, 
                     msisdn
                   FROM cdr c 
                   WHERE  
                    c.eventDate>=a.createdon 
                    AND c.eventDate <= a.actionTakenOn) sub on sub.msisdn=s.msisdn
SET a.exceedusage = sub.val 
WHERE a.remark='Reason : Data limit crossed' 
AND a.exceedusage IS NULL;

I would move the subquery into FROM (actually FROM in select and UPDATE section for the query) to let it be executed just once.

Upvotes: 2

Related Questions