1

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

3
  • Indexes on (ClientID, RecoveryCaseID) would be better for this query. Nov 26, 2015 at 9:39
  • And I wonder why you have 2 joins to detailrecovery. Can't you rewrite with only one join (in the FROM clause) and throw away the correlated inline subquery? Nov 26, 2015 at 9:40
  • And why, why, oh why do the tables not have any primary or unique constraint? Nov 26, 2015 at 9:43

1 Answer 1

3

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;
2
  • Good. It can be improved further by removing the ClientID from the internal group by and removing the external GROUP BY completely. Nov 26, 2015 at 18:42
  • @ypercube, Agreed. was in a rush. Updated.
    – Stoleg
    Nov 27, 2015 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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