Mysql – How to optimize MySQL SUM query even after adding index

explainindexmysql-5.6optimizationperformancequery-performance

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table 1:detail_recovery_case = 67532 records and Table 2: y8qel_detailrecovery = 28638 records. I think its taking more time to execute the SUM() Subquery. Can we re-write or optimize this query in any way. 

   SELECT 
a.RecoveryCaseID,
a.PatientClientID,
a.ClientID,
a.ClientName,
a.CaseOpenedDate,
a.CaseCloseDate,
a.CaseType,
a.CaseStatus,
a.FundingType,
a.PatientFirstName,
a.PatientLastName,
a.PatientDOB,
(a.GreenedClaimsAmt+a.PharmacyClaimsAmt+a.AdditionalClaimsAmt) as caseamount,
(select sum(c.RecoveryAmt) from detailrecovery AS c 
WHERE a.RecoveryCaseID=c.RecoveryCaseID and a.ClientID=c.ClientID) as recoveryamount 
FROM detail_recovery_case AS a 
JOIN detailrecovery AS b ON (a.RecoveryCaseID=b.RecoveryCaseID and a.ClientID=b.ClientID) 
WHERE a.ClientID=50  GROUP BY a.RecoveryCaseID ORDER BY a.RecoveryCaseID desc;

The following is the explain plan which shows its using all the indexes.

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: b
         type: index
possible_keys: rcid
          key: rcid
      key_len: 8
          ref: NULL
         rows: 28742
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: ref
possible_keys: rccid
          key: rccid
      key_len: 8
          ref: frg13b.b.RecoveryCaseID,const
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: c
         type: ref
possible_keys: rcid
          key: rcid
      key_len: 8
          ref: func,const
         rows: 718
        Extra: NULL
3 rows in set (0.25 sec)

I have allocated
1) 4GB for MySQL Buffer Pool
2) tmp_table_size=297M
3) innodb_sort_buffer_size=50M
4) join_buffer_size=50M

Server version: 5.6.21-log MySQL Community Server (GPL)
Windows Server 2008
Ram 8GB
Intel Xeon CPU E5-2630L v2 @ 2.40GHz

(frgadmin@97.74.236.120) [frg13b]> show create table y8qel_detail_recovery_case\G
*************************** 1. row ***************************
       Table: y8qel_detail_recovery_case
Create Table: CREATE TABLE `y8qel_detail_recovery_case` (
  `RecoveryCaseID` int(11) NOT NULL,
  `ClientID` int(11) NOT NULL,
  `CaseOpenedDate` date DEFAULT NULL,
  `CaseType` varchar(50) DEFAULT NULL,
  `CaseStatus` varchar(20) DEFAULT NULL,
  `CaseCloseDate` date DEFAULT NULL,
  `Specialist` varchar(100) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `ClientName` varchar(50) DEFAULT NULL,
  `ClientLocation` varchar(50) DEFAULT NULL,
  `FundingType` varchar(50) DEFAULT NULL,
  `EmployerID` int(11) DEFAULT NULL,
  `EmployerPlanNumber` varchar(50) DEFAULT NULL,
  `EmployerName` varchar(50) DEFAULT NULL,
  `RecoveryStatus` varchar(5) DEFAULT NULL,
  `PlanState` varchar(50) DEFAULT NULL,
  `SubscriberID` int(11) DEFAULT NULL,
  `SubscriberFirstName` varchar(50) DEFAULT NULL,
  `SubscriberLastName` varchar(50) DEFAULT NULL,
  `SubscriberSSN` varchar(9) DEFAULT NULL,
  `SubscriberAddressLine1` varchar(50) DEFAULT NULL,
  `SubscriberAddressLine2` varchar(50) DEFAULT NULL,
  `SubscriberCity` varchar(50) DEFAULT NULL,
  `SubscriberState` varchar(50) DEFAULT NULL,
  `SubscriberZip` varchar(50) DEFAULT NULL,
  `PatientID` int(11) DEFAULT NULL,
  `PatientClientID` varchar(50) DEFAULT NULL,
  `PatientFirstName` varchar(50) DEFAULT NULL,
  `PatientLastName` varchar(50) DEFAULT NULL,
  `PatientGender` char(1) DEFAULT NULL,
  `PatientDOB` date DEFAULT NULL,
  `PatientSSN` varchar(9) DEFAULT NULL,
  `PatientPhone` varchar(11) DEFAULT NULL,
  `InjuryDate` date DEFAULT NULL,
  `AttnyFirm1Name` varchar(50) DEFAULT NULL,
  `Attorney1First` varchar(50) DEFAULT NULL,
  `Attoney1Last` varchar(50) DEFAULT NULL,
  `Attorney1Address1` varchar(50) DEFAULT NULL,
  `Attorney1Address2` varchar(50) DEFAULT NULL,
  `Attorney1City` varchar(50) DEFAULT NULL,
  `Attorney1State` varchar(2) DEFAULT NULL,
  `Attorney1Zip` varchar(5) DEFAULT NULL,
  `Attorney1Phone` varchar(10) DEFAULT NULL,
  `AttnyFirm2Name` varchar(50) DEFAULT NULL,
  `Attorney2First` varchar(50) DEFAULT NULL,
  `Attoney2Last` varchar(50) DEFAULT NULL,
  `Attorney2Address1` varchar(50) DEFAULT NULL,
  `Attorney2Address2` varchar(50) DEFAULT NULL,
  `Attorney2City` varchar(50) DEFAULT NULL,
  `Attorney2State` varchar(2) DEFAULT NULL,
  `Attorney2Zip` varchar(5) DEFAULT NULL,
  `Attorney2Phone` varchar(10) DEFAULT NULL,
  `1PCarrierName` varchar(50) DEFAULT NULL,
  `1PClaimNumber` varchar(50) DEFAULT NULL,
  `1PInsuranceAddress1` varchar(50) DEFAULT NULL,
  `1PInsuranceAddress2` varchar(50) DEFAULT NULL,
  `1PInsuranceCity` varchar(50) DEFAULT NULL,
  `1PInsuranceState` varchar(2) DEFAULT NULL,
  `1PInsuranceZip` varchar(5) DEFAULT NULL,
  `1PInsurancePhone` varchar(10) DEFAULT NULL,
  `3PCarrierName` varchar(50) DEFAULT NULL,
  `3PClaimNumber` varchar(50) DEFAULT NULL,
  `3PInsuranceAddress1` varchar(50) DEFAULT NULL,
  `3PInsuranceAddress2` varchar(50) DEFAULT NULL,
  `3PInsuranceCity` varchar(50) DEFAULT NULL,
  `3PInsuranceState` varchar(2) DEFAULT NULL,
  `3PInsuranceZip` varchar(5) DEFAULT NULL,
  `3PInsurancePhone` varchar(10) DEFAULT NULL,
  `StateOfLoss` varchar(50) DEFAULT NULL,
  `GreenedClaimsAmt` decimal(15,2) DEFAULT NULL,
  `PharmacyClaimsAmt` decimal(15,2) DEFAULT NULL,
  `AdditionalClaimsAmt` decimal(15,2) DEFAULT NULL,
  KEY `pln` (`PatientLastName`),
  KEY `pfn` (`PatientFirstName`),
  KEY `pdob` (`PatientDOB`),
  KEY `pid` (`PatientID`),
  KEY `cod` (`CaseOpenedDate`),
  KEY `ccd` (`CaseCloseDate`),
  KEY `gca` (`GreenedClaimsAmt`),
  KEY `pca` (`PharmacyClaimsAmt`),
  KEY `aca` (`AdditionalClaimsAmt`),
  KEY `rccid` (`RecoveryCaseID`,`ClientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.55 sec)

(frgadmin@97.74.236.120) [frg13b]> show create table y8qel_detailrecovery\G
*************************** 1. row ***************************
       Table: y8qel_detailrecovery
Create Table: CREATE TABLE `y8qel_detailrecovery` (
  `ClientID` int(11) NOT NULL,
  `ClientName` varchar(50) DEFAULT NULL,
  `RecoveryCaseID` int(11) NOT NULL,
  `RecoveryDate` datetime DEFAULT NULL,
  `RecoveredFrom` varchar(50) DEFAULT NULL,
  `RecoveryAmt` decimal(15,2) DEFAULT NULL,
  KEY `rcvd` (`RecoveryDate`),
  KEY `ra` (`RecoveryAmt`),
  KEY `rcid` (`RecoveryCaseID`,`ClientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

Thanks

Best Answer

Try Summing in a separate query and then joining results:

SELECT 
a.RecoveryCaseID,
a.PatientClientID,
a.ClientID,
a.ClientName,
a.CaseOpenedDate,
a.CaseCloseDate,
a.CaseType,
a.CaseStatus,
a.FundingType,
a.PatientFirstName,
a.PatientLastName,
a.PatientDOB,
a.GreenedClaimsAmt+a.PharmacyClaimsAmt+a.AdditionalClaimsAmt as caseamount,

b.recoveryamount 

FROM detail_recovery_case AS a 
JOIN 
   (select RecoveryCaseID, sum(c.RecoveryAmt) as recoveryamount 
    from detailrecovery
    where ClientID = 50
    group by RecoveryCaseID
   ) AS b

    ON a.RecoveryCaseID=b.RecoveryCaseID 

WHERE a.ClientID=50  
ORDER BY a.RecoveryCaseID desc;
Related Question